Структура БД схема v1

PostgreSQL. Центр модели: workspace платит подписку, внутри projects, каждый audit_run = один этап с вердиктом и issues.

Сущности

DDL (ключевые таблицы)

users

CREATE TABLE users (
  id               UUID PRIMARY KEY,
  email            TEXT UNIQUE,
  telegram_chat_id BIGINT UNIQUE,
  name             TEXT,
  locale           TEXT DEFAULT 'ru',
  created_at       TIMESTAMPTZ NOT NULL,
  updated_at       TIMESTAMPTZ NOT NULL
);

workspaces

CREATE TABLE workspaces (
  id            UUID PRIMARY KEY,
  name          TEXT NOT NULL,
  slug          TEXT UNIQUE NOT NULL,
  owner_user_id UUID REFERENCES users(id),
  plan_id       UUID REFERENCES plans(id),
  status        TEXT NOT NULL, -- active|suspended
  created_at    TIMESTAMPTZ NOT NULL
);

workspace_members

CREATE TABLE workspace_members (
  workspace_id UUID REFERENCES workspaces(id),
  user_id      UUID REFERENCES users(id),
  role         TEXT NOT NULL, -- owner|admin|seo|developer|viewer
  invited_at   TIMESTAMPTZ,
  joined_at    TIMESTAMPTZ,
  PRIMARY KEY (workspace_id, user_id)
);

plans.limits_json (пример)

{
  "max_projects": 5,
  "max_runs_per_month": 150,
  "max_members": 5,
  "stages": ["base", "deep", "prod"],
  "history_days": 365,
  "ci_enabled": true,
  "ai_credits": 50
}

subscriptions

CREATE TABLE subscriptions (
  id            UUID PRIMARY KEY,
  workspace_id  UUID REFERENCES workspaces(id),
  plan_id       UUID REFERENCES plans(id),
  status        TEXT NOT NULL, -- trialing|active|past_due|canceled
  billing_cycle TEXT NOT NULL, -- monthly|yearly
  period_start  DATE NOT NULL,
  period_end    DATE NOT NULL,
  provider      TEXT, -- yookassa|invoice|manual
  external_id   TEXT,
  created_at    TIMESTAMPTZ NOT NULL
);

usage_counters

CREATE TABLE usage_counters (
  workspace_id UUID REFERENCES workspaces(id),
  period_ym    TEXT NOT NULL, -- '2026-07'
  runs_used    INT NOT NULL DEFAULT 0,
  runs_limit   INT NOT NULL,
  ai_used      INT NOT NULL DEFAULT 0,
  PRIMARY KEY (workspace_id, period_ym)
);
-- Инкремент при старте audit_run; бот проверяет перед запуском.

projects

CREATE TABLE projects (
  id           UUID PRIMARY KEY,
  workspace_id UUID REFERENCES workspaces(id),
  name         TEXT NOT NULL,
  base_url     TEXT NOT NULL,
  status       TEXT DEFAULT 'active',
  last_run_id  UUID,
  last_verdict TEXT, -- ci_ok|ci_block
  created_at   TIMESTAMPTZ NOT NULL,
  UNIQUE (workspace_id, base_url)
);

audit_runs + audit_issues

CREATE TABLE audit_runs (
  id           UUID PRIMARY KEY,
  project_id   UUID REFERENCES projects(id),
  workspace_id UUID NOT NULL, -- денорм. для дашборда
  stage        TEXT NOT NULL, -- base|deep|prod
  triggered_by UUID REFERENCES users(id),
  source       TEXT NOT NULL, -- telegram|web|cli|ci
  status       TEXT NOT NULL, -- queued|running|done|failed
  verdict      TEXT, -- ci_ok|ci_block
  summary_json JSONB,
  report_text  TEXT,
  started_at   TIMESTAMPTZ,
  finished_at  TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL
);

CREATE TABLE audit_issues (
  id       UUID PRIMARY KEY,
  run_id   UUID REFERENCES audit_runs(id) ON DELETE CASCADE,
  severity TEXT NOT NULL,
  code     TEXT NOT NULL,
  message  TEXT NOT NULL,
  url      TEXT,
  tip_json JSONB
);

CREATE TABLE payments (
  id           UUID PRIMARY KEY,
  workspace_id UUID REFERENCES workspaces(id),
  user_id      UUID REFERENCES users(id),
  type         TEXT NOT NULL, -- subscription|one_off
  amount_rub   INT NOT NULL,
  currency     TEXT DEFAULT 'RUB',
  provider     TEXT NOT NULL,
  status       TEXT NOT NULL, -- pending|paid|failed|refunded
  external_id  TEXT,
  paid_at      TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL
);

CREATE TABLE one_off_orders (
  id           UUID PRIMARY KEY,
  user_id      UUID REFERENCES users(id),
  product_code TEXT NOT NULL, -- audit_stage1|audit_full|pre_release
  target_url   TEXT NOT NULL,
  payment_id   UUID REFERENCES payments(id),
  run_id       UUID REFERENCES audit_runs(id),
  upsell_coupon TEXT,
  created_at   TIMESTAMPTZ NOT NULL
);
-- run без project до апсейла; затем привязка к project

Роли участников

Этапы аудита (stage)

Связи (ER)

users ──< workspace_members >── workspaces
workspaces ──< projects
workspaces ──< subscriptions >── plans
workspaces ──< usage_counters
workspaces ──< payments
projects ──< audit_runs ──< audit_issues
projects ──< ci_integrations
users ──< one_off_orders >── payments
audit_runs ── (optional) one_off_orders.run_id

Индексы

• users(telegram_chat_id) UNIQUE WHERE NOT NULL
• users(email) UNIQUE WHERE NOT NULL
• workspace_members(workspace_id, user_id) UNIQUE
• projects(workspace_id, base_url) UNIQUE
• audit_runs(project_id, created_at DESC)
• usage_counters(workspace_id, period_ym) UNIQUE
• subscriptions(workspace_id) WHERE status = 'active'

Поток: запуск из Telegram

1. Бот получает /stage2 site.ru → lookup user по telegram_chat_id
2. Найти workspace (default) → project по base_url
3. Проверить usage_counters.runs_used < runs_limit
4. INSERT audit_runs (queued) → worker → done + audit_issues
5. UPDATE projects.last_run_id, last_verdict; increment usage_counters

Миграция от текущего бота

Сейчас: chat_id + last_url в памяти.
Шаг 1: users + projects + audit_runs в Postgres.
Шаг 2: workspaces + subscriptions.
Шаг 3: кабинет читает те же таблицы.

SEO Production Doctor · db-schema v1 · 2026