Схема БД

Правила

Все таблицы используют uuid primary key.
Деньги хранятся как numeric(12,2) и currency char(3).
Финансовая история append-only: payment/refund/balance entry не удаляются.
Webhook и интеграционные события имеют уникальные idempotency keys.
Ссылки на identity/LMS/storefront хранятся как UUID/text без FK между сервисами.

Enum-значения

create type crm_account_status as enum ('new','active','paused','archived','blocked');
create type crm_lead_status as enum ('new','contacted','qualified','converted','lost','spam');
create type crm_deal_status as enum ('new','qualified','proposal','won','lost','cancelled');
create type crm_invoice_status as enum ('draft','issued','partially_paid','paid','overdue','cancelled','void');
create type crm_payment_status as enum ('pending','succeeded','failed','refunded','disputed');
create type crm_refund_status as enum ('requested','approved','sent','succeeded','failed','cancelled');
create type crm_entitlement_status as enum ('pending','active','suspended','expired','revoked');
create type crm_ticket_status as enum ('open','pending','resolved','closed','cancelled');
create type crm_payroll_status as enum ('draft','calculated','review','approved','paid','cancelled');
create type crm_product_status as enum ('draft','active','paused','archived');
create type crm_product_run_status as enum ('planned','open','closed','running','finished','cancelled');
create type crm_price_plan_status as enum ('draft','active','retired');

Accounts и contacts

create table crm_accounts (
  id uuid primary key default gen_random_uuid(),
  type text not null check (type in ('family','person','organization','lead')),
  display_name text not null,
  status crm_account_status not null default 'new',
  owner_user_id uuid,
  risk_flags jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_person_links (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id) on delete cascade,
  identity_user_id uuid,
  role text not null check (role in ('payer','student','parent','teacher','contact','guardian')),
  display_name text not null,
  source text not null default 'manual',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index crm_person_links_identity_role_uidx
  on crm_person_links (account_id, identity_user_id, role)
  where identity_user_id is not null;

create table crm_contacts (
  id uuid primary key default gen_random_uuid(),
  account_id uuid references crm_accounts(id) on delete cascade,
  person_link_id uuid references crm_person_links(id) on delete cascade,
  type text not null check (type in ('email','phone','messenger','address')),
  value text not null,
  normalized_value text,
  is_primary boolean not null default false,
  is_verified boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Products and pricing

create table product_types (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title text not null,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table products (
  id uuid primary key default gen_random_uuid(),
  type_id uuid not null references product_types(id),
  slug text not null unique,
  title text not null,
  status crm_product_status not null default 'draft',
  target_domain text not null check (target_domain in ('lms','competitions','task-bank','manual')),
  target_ref text,
  default_entitlement_policy jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table product_runs (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null references products(id),
  title text not null,
  starts_at timestamptz,
  ends_at timestamptz,
  capacity int,
  status crm_product_run_status not null default 'planned',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table price_plans (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null references products(id),
  product_run_id uuid references product_runs(id),
  title text not null,
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  billing_mode text not null check (billing_mode in ('one_time','subscription','installment','manual')),
  status crm_price_plan_status not null default 'draft',
  valid_from timestamptz,
  valid_to timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Leads, deals, orders

create table crm_leads (
  id uuid primary key default gen_random_uuid(),
  account_id uuid references crm_accounts(id),
  source text not null,
  status crm_lead_status not null default 'new',
  campaign_ref jsonb,
  payload jsonb not null default '{}'::jsonb,
  owner_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_deals (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  lead_id uuid references crm_leads(id),
  status crm_deal_status not null default 'new',
  source text not null,
  amount numeric(12,2),
  currency char(3) not null default 'RUB',
  owner_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_orders (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  deal_id uuid references crm_deals(id),
  purchaser_user_id uuid,
  payer_person_link_id uuid references crm_person_links(id),
  beneficiary_type text not null check (beneficiary_type in ('student_profile','user','family_group','organization','manual')),
  beneficiary_ref text,
  enrollment_subject_type text check (enrollment_subject_type in ('student_profile','user','organization_student','manual')),
  enrollment_subject_ref text,
  status text not null check (status in ('draft','confirmed','cancelled','fulfilled')),
  amount_total numeric(12,2) not null default 0,
  currency char(3) not null default 'RUB',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check ((beneficiary_type = 'manual') or beneficiary_ref is not null),
  check ((enrollment_subject_type is null and enrollment_subject_ref is null) or (enrollment_subject_type is not null and enrollment_subject_ref is not null))
);

create table crm_order_items (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references crm_orders(id) on delete cascade,
  product_id uuid references products(id),
  product_run_id uuid references product_runs(id),
  price_plan_id uuid references price_plans(id),
  title text not null,
  quantity int not null default 1,
  unit_price numeric(12,2) not null,
  discount_amount numeric(12,2) not null default 0,
  metadata jsonb not null default '{}'::jsonb
);

Billing

create table crm_invoices (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  order_id uuid references crm_orders(id),
  status crm_invoice_status not null default 'draft',
  amount_total numeric(12,2) not null,
  amount_paid numeric(12,2) not null default 0,
  currency char(3) not null default 'RUB',
  due_at timestamptz,
  issued_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_invoice_items (
  id uuid primary key default gen_random_uuid(),
  invoice_id uuid not null references crm_invoices(id) on delete cascade,
  title text not null,
  product_id uuid references products(id),
  product_run_id uuid references product_runs(id),
  price_plan_id uuid references price_plans(id),
  amount numeric(12,2) not null,
  metadata jsonb not null default '{}'::jsonb
);

create table crm_payments (
  id uuid primary key default gen_random_uuid(),
  invoice_id uuid references crm_invoices(id),
  account_id uuid not null references crm_accounts(id),
  provider text not null,
  provider_payment_id text,
  idempotency_key text not null unique,
  status crm_payment_status not null default 'pending',
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  paid_at timestamptz,
  raw_payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (provider, provider_payment_id)
);

create table crm_payment_webhooks (
  id uuid primary key default gen_random_uuid(),
  provider text not null,
  provider_event_id text not null,
  signature_valid boolean not null,
  idempotency_key text not null unique,
  status text not null check (status in ('received','processed','ignored','failed')),
  payload jsonb not null,
  error_message text,
  created_at timestamptz not null default now(),
  unique (provider, provider_event_id)
);

create table crm_refunds (
  id uuid primary key default gen_random_uuid(),
  payment_id uuid not null references crm_payments(id),
  account_id uuid not null references crm_accounts(id),
  status crm_refund_status not null default 'requested',
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  reason text not null,
  approved_by_user_id uuid,
  provider_refund_id text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_balance_entries (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  type text not null check (type in ('invoice_debit','payment_credit','refund_debit','adjustment_credit','adjustment_debit','writeoff')),
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  source_type text not null,
  source_id uuid not null,
  reason text,
  created_at timestamptz not null default now()
);

create table crm_adjustments (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  type text not null check (type in ('discount','writeoff','balance_transfer','correction')),
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  reason text not null,
  actor_user_id uuid,
  created_at timestamptz not null default now()
);

create index crm_balance_entries_account_idx
  on crm_balance_entries (account_id, created_at desc);

Entitlement, support, payroll

create table crm_entitlements (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  purchaser_user_id uuid,
  beneficiary_type text not null check (beneficiary_type in ('student_profile','user','family_group','organization','manual')),
  beneficiary_ref text,
  enrollment_subject_type text check (enrollment_subject_type in ('student_profile','user','organization_student','manual')),
  enrollment_subject_ref text,
  product_id uuid references products(id),
  product_run_id uuid references product_runs(id),
  price_plan_id uuid references price_plans(id),
  target_domain text not null check (target_domain in ('lms','competitions','task-bank')),
  target_ref text,
  status crm_entitlement_status not null default 'pending',
  source_type text not null,
  source_id uuid not null,
  starts_at timestamptz,
  ends_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check ((beneficiary_type = 'manual') or beneficiary_ref is not null),
  check ((enrollment_subject_type is null and enrollment_subject_ref is null) or (enrollment_subject_type is not null and enrollment_subject_ref is not null))
);

create table crm_entitlement_consumption_logs (
  id uuid primary key default gen_random_uuid(),
  entitlement_id uuid not null references crm_entitlements(id),
  consumer_domain text not null check (consumer_domain in ('lms','competitions','task-bank')),
  consumer_ref jsonb not null,
  quantity numeric(12,2) not null default 1,
  status text not null check (status in ('recorded','reverted')),
  idempotency_key text not null unique,
  occurred_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table crm_interactions (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  type text not null check (type in ('call','email','message','meeting','note')),
  direction text check (direction in ('inbound','outbound','internal')),
  body text,
  actor_user_id uuid,
  occurred_at timestamptz not null default now()
);

create table crm_tickets (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  status crm_ticket_status not null default 'open',
  priority text not null default 'normal',
  subject text not null,
  assignee_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_notes (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references crm_accounts(id),
  author_user_id uuid not null,
  visibility text not null check (visibility in ('internal','support','finance','restricted')),
  body text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_teacher_rates (
  id uuid primary key default gen_random_uuid(),
  teacher_user_id uuid not null,
  rate_type text not null check (rate_type in ('lesson','checking','consultation','fixed')),
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  valid_from date not null,
  valid_to date,
  created_at timestamptz not null default now()
);

create table crm_teacher_payouts (
  id uuid primary key default gen_random_uuid(),
  teacher_user_id uuid not null,
  period_start date not null,
  period_end date not null,
  status crm_payroll_status not null default 'draft',
  amount_total numeric(12,2) not null default 0,
  approved_by_user_id uuid,
  approved_at timestamptz,
  paid_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table crm_payable_activities (
  id uuid primary key default gen_random_uuid(),
  teacher_user_id uuid not null,
  source_domain text not null,
  source_type text not null,
  source_id text not null,
  occurred_at timestamptz not null,
  duration_minutes int,
  status text not null default 'pending',
  created_at timestamptz not null default now(),
  unique (source_domain, source_type, source_id)
);

create table crm_payroll_periods (
  id uuid primary key default gen_random_uuid(),
  period_start date not null,
  period_end date not null,
  status crm_payroll_status not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (period_start, period_end)
);

create table crm_payroll_items (
  id uuid primary key default gen_random_uuid(),
  payroll_period_id uuid not null references crm_payroll_periods(id),
  payout_id uuid references crm_teacher_payouts(id),
  payable_activity_id uuid references crm_payable_activities(id),
  rate_id uuid references crm_teacher_rates(id),
  amount numeric(12,2) not null,
  currency char(3) not null default 'RUB',
  source_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

Sync и audit

create table crm_sync_logs (
  id uuid primary key default gen_random_uuid(),
  source_domain text not null,
  source_event_id text not null,
  idempotency_key text not null unique,
  status text not null check (status in ('received','processed','ignored','failed')),
  error_message text,
  created_at timestamptz not null default now(),
  unique (source_domain, source_event_id)
);

create table crm_audit_logs (
  id uuid primary key default gen_random_uuid(),
  actor_user_id uuid,
  action text not null,
  entity_type text not null,
  entity_id uuid,
  before jsonb,
  after jsonb,
  reason text,
  request_id text,
  created_at timestamptz not null default now()
);

create index crm_audit_logs_entity_idx
  on crm_audit_logs (entity_type, entity_id, created_at desc);

Правила​

Enum-значения​

Accounts и contacts​

Products and pricing​

Leads, deals, orders​

Billing​

Entitlement, support, payroll​

Sync и audit​

Правила

Enum-значения

Accounts и contacts

Products and pricing

Leads, deals, orders

Billing

Entitlement, support, payroll

Sync и audit