Схема БД

Правила

• Все таблицы используют uuid primary key.
• Все mutable-таблицы имеют created_at, updated_at.
• Published versions не обновляются in-place.
• Для source_ref используются поля source_domain, source_type, source_id.
• JSONB разрешён для block props, SEO, facets, visibility rules и form schema.
• PII из forms хранится минимально и не попадает в audit payload.

Enum-значения

create type storefront_publication_status as enum ('draft','review','scheduled','published','hidden','paused','archived');
create type storefront_version_status as enum ('draft','review','scheduled','published','retired');
create type storefront_moderation_status as enum ('draft','in_review','approved','rejected','published','hidden','archived');
create type storefront_form_submission_status as enum ('received','sent_to_crm','failed','discarded');
create type storefront_indexing_policy as enum ('index','noindex','private');

Pages и blocks

create table storefront_pages (
  id uuid primary key default gen_random_uuid(),
  slug text not null,
  locale text not null default 'ru',
  type text not null check (type in ('landing','catalog','profile','article','campaign','system')),
  status storefront_publication_status not null default 'draft',
  primary_source_domain text,
  primary_source_type text,
  primary_source_id text,
  canonical_url text,
  indexing_policy storefront_indexing_policy not null default 'index',
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (slug, locale)
);

create table storefront_page_versions (
  id uuid primary key default gen_random_uuid(),
  page_id uuid not null references storefront_pages(id) on delete cascade,
  version int not null,
  status storefront_version_status not null default 'draft',
  seo jsonb not null default '{}'::jsonb,
  scheduled_for timestamptz,
  published_at timestamptz,
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  unique (page_id, version)
);

create table storefront_blocks (
  id uuid primary key default gen_random_uuid(),
  page_version_id uuid not null references storefront_page_versions(id) on delete cascade,
  type text not null,
  position int not null,
  props jsonb not null default '{}'::jsonb,
  data_source jsonb not null default '{}'::jsonb,
  visibility_rule jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (page_version_id, position)
);

create index storefront_blocks_props_gin
  on storefront_blocks using gin (props);

Каталог и подборки

create table storefront_catalog_items (
  id uuid primary key default gen_random_uuid(),
  source_domain text not null check (source_domain in ('crm','lms','competitions','task-bank','manual')),
  source_type text not null,
  source_id text not null,
  slug text not null unique,
  title text not null,
  status storefront_publication_status not null default 'draft',
  facets jsonb not null default '{}'::jsonb,
  display jsonb not null default '{}'::jsonb,
  availability jsonb not null default '{}'::jsonb,
  pricing_summary jsonb not null default '{}'::jsonb,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (source_domain, source_type, source_id)
);

create index storefront_catalog_items_facets_gin
  on storefront_catalog_items using gin (facets);

create table storefront_collections (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  type text not null check (type in ('manual','rule_based','hybrid')),
  rule jsonb not null default '{}'::jsonb,
  status storefront_publication_status not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_collection_items (
  id uuid primary key default gen_random_uuid(),
  collection_id uuid not null references storefront_collections(id) on delete cascade,
  catalog_item_id uuid not null references storefront_catalog_items(id),
  position int not null,
  display_override jsonb not null default '{}'::jsonb,
  starts_at timestamptz,
  ends_at timestamptz,
  unique (collection_id, catalog_item_id),
  unique (collection_id, position)
);

Публичные профили

create table storefront_public_profiles (
  id uuid primary key default gen_random_uuid(),
  type text not null check (type in ('person','student','alumni','team','organization','committee')),
  source_domain text,
  source_type text,
  source_id text,
  slug text not null unique,
  public_name text not null,
  status storefront_publication_status not null default 'draft',
  privacy_policy jsonb not null default '{}'::jsonb,
  consent_status text not null default 'unknown',
  display jsonb not null default '{}'::jsonb,
  seo jsonb not null default '{}'::jsonb,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_profile_roles (
  id uuid primary key default gen_random_uuid(),
  profile_id uuid not null references storefront_public_profiles(id) on delete cascade,
  context_domain text not null,
  context_type text not null,
  context_id text not null,
  role_label text not null,
  position int,
  status storefront_publication_status not null default 'draft'
);

Отзывы, достижения и факты

create table storefront_reviews (
  id uuid primary key default gen_random_uuid(),
  author_public_name text not null,
  author_type text not null check (author_type in ('student','parent','alumni','partner','other')),
  rating int check (rating between 1 and 5),
  body text not null,
  source_domain text,
  source_type text,
  source_id text,
  display_places jsonb not null default '[]'::jsonb,
  moderation_status storefront_moderation_status not null default 'draft',
  consent_status text not null default 'unknown',
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_rating_aggregates (
  id uuid primary key default gen_random_uuid(),
  target_domain text not null,
  target_type text not null,
  target_id text not null,
  rating numeric(3,2) not null,
  reviews_count int not null,
  rule jsonb not null default '{}'::jsonb,
  calculated_at timestamptz not null,
  unique (target_domain, target_type, target_id)
);

create table storefront_achievements (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  type text not null,
  subject_domain text not null,
  subject_type text not null,
  subject_id text not null,
  period_start date,
  period_end date,
  source jsonb not null default '{}'::jsonb,
  moderation_status storefront_moderation_status not null default 'draft',
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_facts (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  type text not null check (type in ('number','text','badge','statistic','certification','media')),
  public_text text not null,
  value jsonb not null,
  unit text,
  source jsonb not null default '{}'::jsonb,
  calculation_method text,
  validity_policy jsonb not null default '{}'::jsonb,
  verification_status text not null check (verification_status in ('draft','needs_review','verified','expired','rejected')),
  status storefront_publication_status not null default 'draft',
  last_verified_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Кампании, формы и SEO

create table storefront_campaigns (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  source_domain text,
  source_type text,
  source_id text,
  starts_at timestamptz,
  ends_at timestamptz,
  status storefront_publication_status not null default 'draft',
  tracking jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_forms (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title text not null,
  schema jsonb not null,
  crm_mapping jsonb not null,
  consent_text text not null,
  spam_policy jsonb not null default '{}'::jsonb,
  status storefront_publication_status not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_form_submissions (
  id uuid primary key default gen_random_uuid(),
  form_id uuid not null references storefront_forms(id),
  page_id uuid references storefront_pages(id),
  payload_redacted jsonb not null default '{}'::jsonb,
  payload_encrypted jsonb,
  pii_classification text not null check (pii_classification in ('none','contact','child_data','sensitive')) default 'contact',
  consent_version text not null,
  retention_until timestamptz not null,
  anonymized_at timestamptz,
  status storefront_form_submission_status not null default 'received',
  idempotency_key text not null unique,
  crm_lead_id uuid,
  error_message text,
  created_at timestamptz not null default now(),
  sent_to_crm_at timestamptz,
  check (payload_encrypted is not null or anonymized_at is not null)
);

-- Raw form payload is PII. Storefront keeps only payload_redacted for operational read models.
-- Raw values are encrypted in payload_encrypted, sent to CRM, then cleared/anonymized by retention_until.

create table storefront_redirects (
  id uuid primary key default gen_random_uuid(),
  from_path text not null unique,
  to_url text not null,
  status_code int not null check (status_code in (301,302,307,308)),
  is_external boolean not null default false,
  allowlist_status text not null default 'internal',
  status storefront_publication_status not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table storefront_sitemap_entries (
  id uuid primary key default gen_random_uuid(),
  page_version_id uuid references storefront_page_versions(id),
  url text not null unique,
  locale text not null default 'ru',
  lastmod timestamptz not null,
  priority numeric(2,1),
  changefreq text,
  is_indexable boolean not null default true
);

Модерация и audit

create table storefront_moderation_cases (
  id uuid primary key default gen_random_uuid(),
  entity_type text not null,
  entity_id uuid not null,
  status text not null check (status in ('open','approved','rejected','needs_changes','closed')),
  reason text,
  reviewer_user_id uuid,
  decision_at timestamptz,
  created_at timestamptz not null default now()
);

create table storefront_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,
  request_id text,
  created_at timestamptz not null default now()
);

create index storefront_audit_logs_entity_idx
  on storefront_audit_logs (entity_type, entity_id, created_at desc);