Схема БД

Зачем нужно

Документ фиксирует целевую PostgreSQL-схему identity: типы колонок, nullable, связи, индексы, уникальность, правила удаления и retention. Если реализация использует ORM, миграции должны приводить к эквивалентной схеме.

Общие правила

База: PostgreSQL 16+.
Primary key: UUID DEFAULT gen_random_uuid().
Время: TIMESTAMPTZ, хранение в UTC.
JSON: JSONB, только для расширяемых metadata/settings, не для обязательных связей.
Soft delete: deleted_at TIMESTAMPTZ, если сущность может быть скрыта без потери аудита.
Секреты: только hash или encrypted value, никогда plain text.
PII в индексах нормализуется: email lowercase, phone E.164.
Все таблицы с бизнес-изменениями имеют audit event.

Extensions

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

Enums

CREATE TYPE user_status AS ENUM ('pending', 'active', 'blocked', 'deleted');
CREATE TYPE contact_kind AS ENUM ('email', 'phone');
CREATE TYPE auth_method_type AS ENUM ('password', 'email_otp', 'sms_otp', 'totp', 'oauth_provider');
CREATE TYPE verification_purpose AS ENUM ('contact_verify', 'login', 'reset_password', 'sso_otc');
CREATE TYPE session_status AS ENUM ('active', 'revoked', 'expired', 'compromised');
CREATE TYPE role_scope AS ENUM ('global', 'organization', 'team');
CREATE TYPE family_role AS ENUM ('adult');
CREATE TYPE membership_status AS ENUM ('pending', 'active', 'suspended', 'removed');
CREATE TYPE invitation_status AS ENUM ('pending', 'accepted', 'rejected', 'revoked', 'expired');
CREATE TYPE organization_type AS ENUM ('systematika', 'school', 'private_school', 'club', 'education_center', 'commercial_center', 'franchise', 'foreign_organization', 'other');
CREATE TYPE organization_status AS ENUM ('draft', 'active', 'unclaimed', 'disputed', 'archived', 'merged');
CREATE TYPE organization_duplicate_status AS ENUM ('none', 'possible_duplicate', 'confirmed_duplicate', 'merged');
CREATE TYPE organization_membership_status AS ENUM ('requested', 'invited', 'active', 'rejected', 'suspended', 'left');
CREATE TYPE organization_membership_source AS ENUM ('self_request', 'invitation', 'admin_created', 'ownership_claim', 'system_migration', 'system_action');
CREATE TYPE organization_invitation_status AS ENUM ('created', 'sent', 'accepted', 'expired', 'revoked', 'rejected');
CREATE TYPE organization_claim_status AS ENUM ('submitted', 'needs_more_info', 'approved', 'rejected', 'cancelled', 'disputed');
CREATE TYPE organization_transfer_status AS ENUM ('created', 'sent', 'accepted', 'expired', 'cancelled', 'completed');
CREATE TYPE organization_student_status AS ENUM ('active', 'archived', 'possible_duplicate');
CREATE TYPE organization_merge_status AS ENUM ('planned', 'confirmed', 'completed', 'cancelled', 'failed');
CREATE TYPE oauth_client_type AS ENUM ('public', 'confidential');
CREATE TYPE oauth_client_status AS ENUM ('active', 'disabled', 'deleted');
CREATE TYPE actor_context_kind AS ENUM ('personal', 'child_delegated', 'family_adult', 'organization_member', 'team_member', 'admin', 'oauth_client', 'plugin');
CREATE TYPE device_binding_status AS ENUM ('active', 'revoked', 'expired');
CREATE TYPE child_device_authorization_status AS ENUM ('created', 'approved', 'completed', 'revoked', 'expired');

Users

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status user_status NOT NULL DEFAULT 'pending',
  display_name VARCHAR(160) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  avatar_url TEXT,
  locale VARCHAR(16) NOT NULL DEFAULT 'ru',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/Moscow',
  metadata JSONB NOT NULL DEFAULT '{}',
  last_login_at TIMESTAMPTZ,
  blocked_at TIMESTAMPTZ,
  blocked_reason TEXT,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (display_name <> '')
);

CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_last_login_at ON users(last_login_at DESC);
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NOT NULL;

CREATE TABLE user_emails (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  email CITEXT NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(email),
  CHECK ((is_verified = FALSE AND verified_at IS NULL) OR (is_verified = TRUE AND verified_at IS NOT NULL))
);

CREATE UNIQUE INDEX uq_user_emails_primary ON user_emails(user_id) WHERE is_primary;
CREATE INDEX idx_user_emails_user_id ON user_emails(user_id);

CREATE TABLE user_phones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  phone VARCHAR(32) NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(phone),
  CHECK (phone ~ '^\\+[1-9][0-9]{7,14}$'),
  CHECK ((is_verified = FALSE AND verified_at IS NULL) OR (is_verified = TRUE AND verified_at IS NOT NULL))
);

CREATE UNIQUE INDEX uq_user_phones_primary ON user_phones(user_id) WHERE is_primary;
CREATE INDEX idx_user_phones_user_id ON user_phones(user_id);

CREATE TABLE user_field_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key VARCHAR(80) NOT NULL UNIQUE,
  type VARCHAR(40) NOT NULL,
  label JSONB NOT NULL,
  validation JSONB NOT NULL DEFAULT '{}',
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE user_field_values (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  field_id UUID NOT NULL REFERENCES user_field_definitions(id) ON DELETE RESTRICT,
  value JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, field_id)
);

Auth

CREATE TABLE user_credentials (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  password_hash TEXT NOT NULL,
  password_changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
  failed_attempts INTEGER NOT NULL DEFAULT 0,
  locked_until TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE user_auth_methods (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type auth_method_type NOT NULL,
  provider VARCHAR(80),
  external_subject VARCHAR(255),
  secret_encrypted TEXT,
  settings JSONB NOT NULL DEFAULT '{}',
  is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  last_used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, type, provider),
  UNIQUE(provider, external_subject)
);

CREATE INDEX idx_user_auth_methods_user_id ON user_auth_methods(user_id);
CREATE INDEX idx_user_auth_methods_provider_subject ON user_auth_methods(provider, external_subject);

CREATE TABLE auth_flow_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flow_type VARCHAR(40) NOT NULL,
  identifier VARCHAR(255),
  resolved_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  current_step VARCHAR(80) NOT NULL,
  state JSONB NOT NULL DEFAULT '{}',
  ip_address INET,
  user_agent TEXT,
  expires_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_auth_flow_sessions_expires_at ON auth_flow_sessions(expires_at);
CREATE INDEX idx_auth_flow_sessions_identifier ON auth_flow_sessions(identifier);

CREATE TABLE auth_flow_steps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flow VARCHAR(20) NOT NULL CHECK (flow IN ('login','registration')),
  step_id VARCHAR(100) NOT NULL,
  mandatory BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INT NOT NULL,
  config JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(flow, step_id)
);

CREATE TABLE verification_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  contact_kind contact_kind NOT NULL,
  destination VARCHAR(255) NOT NULL,
  purpose verification_purpose NOT NULL,
  code_hash TEXT NOT NULL,
  attempts_count INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 5,
  expires_at TIMESTAMPTZ NOT NULL,
  consumed_at TIMESTAMPTZ,
  invalidated_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_verification_codes_lookup ON verification_codes(destination, purpose, expires_at);
CREATE INDEX idx_verification_codes_user_id ON verification_codes(user_id);

CREATE TABLE user_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status session_status NOT NULL DEFAULT 'active',
  refresh_token_hash TEXT NOT NULL UNIQUE,
  refresh_token_family_id UUID NOT NULL,
  device_id VARCHAR(128),
  device_name VARCHAR(160),
  ip_address INET,
  user_agent TEXT,
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  revoke_reason VARCHAR(80),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_user_sessions_user_active ON user_sessions(user_id, status);
CREATE INDEX idx_user_sessions_family ON user_sessions(refresh_token_family_id);
CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at);

CREATE TABLE refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  session_id UUID NOT NULL REFERENCES user_sessions(id) ON DELETE CASCADE,
  token_hash TEXT NOT NULL UNIQUE,
  token_family_id UUID NOT NULL,
  rotated_from_token_id UUID REFERENCES refresh_tokens(id) ON DELETE SET NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id, expires_at);
CREATE INDEX idx_refresh_tokens_family ON refresh_tokens(token_family_id);

CREATE TABLE saved_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_fingerprint_hash TEXT NOT NULL,
  display_name_snapshot VARCHAR(160) NOT NULL,
  last_used_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, device_fingerprint_hash)
);

CREATE TABLE device_bindings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  saved_account_id UUID REFERENCES saved_accounts(id) ON DELETE SET NULL,
  device_fingerprint_hash TEXT NOT NULL,
  status device_binding_status NOT NULL DEFAULT 'active',
  trusted_until TIMESTAMPTZ,
  last_seen_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, device_fingerprint_hash)
);

CREATE INDEX idx_device_bindings_user_status ON device_bindings(user_id, status);

RBAC

CREATE TABLE permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(120) NOT NULL UNIQUE,
  domain VARCHAR(40) NOT NULL,
  resource VARCHAR(80) NOT NULL,
  action VARCHAR(80) NOT NULL,
  scope VARCHAR(40),
  display_name JSONB NOT NULL,
  description JSONB,
  is_system BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key VARCHAR(80) NOT NULL,
  scope role_scope NOT NULL,
  parent_id UUID REFERENCES roles(id),
  name JSONB NOT NULL,
  description JSONB,
  order_index INTEGER NOT NULL DEFAULT 100,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(key, scope)
);

CREATE TABLE role_permissions (
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY(role_id, permission_id)
);

CREATE TABLE user_role_assignments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
  assigned_by UUID REFERENCES users(id) ON DELETE SET NULL,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, role_id)
);

CREATE INDEX idx_user_role_assignments_user ON user_role_assignments(user_id);

user_role_assignments используется только для global roles. Organization/team roles назначаются через organization_memberships.role_id и organization_team_memberships.role_id, чтобы не смешивать scoped membership lifecycle с global RBAC assignments.

Family

CREATE TABLE family_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(160) NOT NULL,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE user_family_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  family_group_id UUID NOT NULL REFERENCES family_groups(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role family_role NOT NULL,
  status membership_status NOT NULL DEFAULT 'active',
  label VARCHAR(100),
  invited_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  removed_at TIMESTAMPTZ,
  UNIQUE(family_group_id, user_id),
  CHECK (role = 'adult')
);

CREATE TABLE student_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  family_group_id UUID NOT NULL REFERENCES family_groups(id) ON DELETE CASCADE,
  linked_user_id UUID UNIQUE REFERENCES users(id) ON DELETE SET NULL,
  display_name VARCHAR(160) NOT NULL,
  birth_date DATE,
  grade_level VARCHAR(40),
  status VARCHAR(40) NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  archived_at TIMESTAMPTZ
);

CREATE INDEX idx_student_profiles_family ON student_profiles(family_group_id, status);
CREATE INDEX idx_student_profiles_linked_user ON student_profiles(linked_user_id);

CREATE TABLE student_protected_attributes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_profile_id UUID NOT NULL REFERENCES student_profiles(id) ON DELETE CASCADE,
  attribute_key VARCHAR(80) NOT NULL CHECK (attribute_key IN ('snils','official_birth_date')),
  encrypted_value_ref JSONB NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active' CHECK (status IN ('active','needs_verification','rejected','archived')),
  source VARCHAR(80) NOT NULL CHECK (source IN ('parent','student','teacher_official_flow','admin','import')),
  collected_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  verified_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(student_profile_id, attribute_key)
);

CREATE TABLE family_contact_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_profile_id UUID NOT NULL REFERENCES student_profiles(id) ON DELETE CASCADE,
  family_group_id UUID REFERENCES family_groups(id) ON DELETE SET NULL,
  email CITEXT,
  phone VARCHAR(32),
  source VARCHAR(80) NOT NULL CHECK (source IN ('parent','teacher_official_flow','admin','import')),
  status VARCHAR(40) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','confirmed','rejected','archived')),
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  confirmed_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  confirmed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (email IS NOT NULL OR phone IS NOT NULL)
);

-- family_contact_records store adult contact evidence for family/claim flows.
-- They are not child login through parent contact and do not replace child_device_authorization.

CREATE TABLE parent_account_invites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_profile_id UUID NOT NULL REFERENCES student_profiles(id) ON DELETE CASCADE,
  family_contact_record_id UUID REFERENCES family_contact_records(id) ON DELETE SET NULL,
  email CITEXT,
  phone VARCHAR(32),
  token_hash TEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'created' CHECK (status IN ('created','sent','accepted','expired','revoked')),
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  accepted_at TIMESTAMPTZ,
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (email IS NOT NULL OR phone IS NOT NULL)
);

-- parent_account_invites invite an adult to create/confirm a parent account.
-- They are not a child auth flow; linked child device login uses child_device_authorization.

-- Post-MVP: service-level parent controls, if approved, should be modeled
-- as family-wide settings for student_profile and must not create per-adult
-- access exceptions while MVP rule "any active adult sees all family children"
-- remains canonical.
-- Example future table name: student_service_settings.

CREATE TABLE delegated_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  subject_student_profile_id UUID NOT NULL REFERENCES student_profiles(id) ON DELETE CASCADE,
  subject_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  family_group_id UUID NOT NULL REFERENCES family_groups(id) ON DELETE CASCADE,
  service_key VARCHAR(80),
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  end_reason VARCHAR(80)
);

CREATE TABLE child_device_authorizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  family_group_id UUID NOT NULL REFERENCES family_groups(id) ON DELETE CASCADE,
  student_profile_id UUID NOT NULL REFERENCES student_profiles(id) ON DELETE CASCADE,
  child_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  approved_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  device_binding_id UUID REFERENCES device_bindings(id) ON DELETE SET NULL,
  device_fingerprint_hash TEXT,
  request_code_hash TEXT,
  qr_token_hash TEXT,
  status child_device_authorization_status NOT NULL DEFAULT 'created',
  requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  approved_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (request_code_hash IS NOT NULL OR qr_token_hash IS NOT NULL),
  CHECK (approved_by_user_id IS NULL OR child_user_id IS NULL OR approved_by_user_id <> child_user_id),
  CHECK (status <> 'approved' OR approved_at IS NOT NULL),
  CHECK (status <> 'completed' OR (approved_at IS NOT NULL AND completed_at IS NOT NULL)),
  CHECK (status <> 'revoked' OR revoked_at IS NOT NULL)
);

CREATE INDEX idx_child_device_authorizations_student ON child_device_authorizations(student_profile_id, status);
CREATE INDEX idx_child_device_authorizations_expires ON child_device_authorizations(expires_at);

Organizations

CREATE TABLE countries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(16) NOT NULL UNIQUE,
  name_ru TEXT NOT NULL,
  name_en TEXT,
  status TEXT NOT NULL CHECK (status IN ('active','hidden')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE regions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  country_id UUID NOT NULL REFERENCES countries(id),
  name TEXT NOT NULL,
  type TEXT,
  status TEXT NOT NULL CHECK (status IN ('active','hidden')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_regions_country ON regions(country_id, status);

CREATE TABLE organization_references (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  country_id UUID NOT NULL REFERENCES countries(id),
  region_id UUID REFERENCES regions(id),
  settlement TEXT NOT NULL,
  name TEXT NOT NULL,
  normalized_name TEXT NOT NULL,
  external_registry_id TEXT,
  status TEXT NOT NULL CHECK (status IN ('active','hidden','deprecated')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_organization_references_lookup
  ON organization_references(country_id, region_id, settlement, normalized_name);

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type organization_type NOT NULL,
  country_id UUID REFERENCES countries(id),
  region_id UUID REFERENCES regions(id),
  settlement TEXT,
  name TEXT NOT NULL,
  normalized_name TEXT NOT NULL,
  reference_id UUID REFERENCES organization_references(id),
  status organization_status NOT NULL DEFAULT 'draft',
  owner_membership_id UUID,
  duplicate_status organization_duplicate_status NOT NULL DEFAULT 'none',
  merged_into_organization_id UUID REFERENCES organizations(id),
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  metadata JSONB NOT NULL DEFAULT '{}',
  verified_at TIMESTAMPTZ,
  archived_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    (status <> 'merged' AND merged_into_organization_id IS NULL)
    OR
    (status = 'merged' AND merged_into_organization_id IS NOT NULL)
  )
);

CREATE INDEX idx_organizations_status ON organizations(status);
CREATE INDEX idx_organizations_reference ON organizations(reference_id);
CREATE INDEX idx_organizations_lookup ON organizations(country_id, region_id, settlement, normalized_name);

CREATE TABLE organization_emails (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email CITEXT NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_organization_emails_primary
  ON organization_emails(organization_id)
  WHERE is_primary;

CREATE TABLE organization_phones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  phone VARCHAR(32) NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_organization_phones_primary
  ON organization_phones(organization_id)
  WHERE is_primary;

CREATE TABLE organization_addresses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  address TEXT NOT NULL,
  type VARCHAR(50),
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_organization_addresses_primary
  ON organization_addresses(organization_id)
  WHERE is_primary;

CREATE TABLE organization_websites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE organization_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  display_name JSONB NOT NULL,
  permissions JSONB NOT NULL DEFAULT '[]',
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  order_index INTEGER NOT NULL DEFAULT 100,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(organization_id, name)
);

CREATE TABLE organization_memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status organization_membership_status NOT NULL DEFAULT 'requested',
  source organization_membership_source NOT NULL,
  role_id UUID NOT NULL REFERENCES organization_roles(id),
  verified_level TEXT,
  verified_checks JSONB NOT NULL DEFAULT '{}',
  invited_by_membership_id UUID REFERENCES organization_memberships(id),
  approved_by_membership_id UUID REFERENCES organization_memberships(id),
  approved_at TIMESTAMPTZ,
  suspended_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(organization_id, user_id)
);

CREATE UNIQUE INDEX uq_organization_memberships_active_user_org
  ON organization_memberships(organization_id, user_id)
  WHERE status IN ('requested','invited','active','suspended');

ALTER TABLE organizations
  ADD CONSTRAINT fk_organizations_owner_membership
  FOREIGN KEY (owner_membership_id)
  REFERENCES organization_memberships(id);

CREATE TABLE organization_invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  invited_by_membership_id UUID NOT NULL REFERENCES organization_memberships(id),
  email CITEXT,
  phone VARCHAR(32),
  invited_user_id UUID REFERENCES users(id),
  delivery_channel TEXT NOT NULL CHECK (delivery_channel IN ('link','email','cabinet','sms')),
  token_hash TEXT UNIQUE,
  proposed_role_id UUID NOT NULL REFERENCES organization_roles(id),
  status organization_invitation_status NOT NULL DEFAULT 'created',
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_by_user_id UUID REFERENCES users(id),
  accepted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    email IS NOT NULL
    OR phone IS NOT NULL
    OR invited_user_id IS NOT NULL
    OR delivery_channel = 'link'
  ),
  CHECK (
    delivery_channel NOT IN ('link','email','sms')
    OR token_hash IS NOT NULL
  ),
  CHECK (
    delivery_channel <> 'email' OR email IS NOT NULL
  ),
  CHECK (
    delivery_channel <> 'sms' OR phone IS NOT NULL
  ),
  CHECK (
    delivery_channel <> 'cabinet' OR invited_user_id IS NOT NULL
  )
);

CREATE INDEX idx_organization_invitations_org_status
  ON organization_invitations(organization_id, status);

CREATE TABLE organization_ownership_claims (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  requested_by_user_id UUID NOT NULL REFERENCES users(id),
  requested_membership_id UUID REFERENCES organization_memberships(id),
  status organization_claim_status NOT NULL DEFAULT 'submitted',
  applicant_full_name TEXT NOT NULL,
  applicant_position TEXT,
  applicant_comment TEXT,
  evidence_links JSONB NOT NULL DEFAULT '[]',
  reviewed_by_user_id UUID REFERENCES users(id),
  reviewed_at TIMESTAMPTZ,
  decision_comment TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_organization_claims_status
  ON organization_ownership_claims(status, created_at DESC);

CREATE TABLE organization_ownership_transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  from_membership_id UUID NOT NULL REFERENCES organization_memberships(id),
  to_membership_id UUID REFERENCES organization_memberships(id),
  to_email CITEXT,
  status organization_transfer_status NOT NULL DEFAULT 'created',
  token_hash TEXT,
  old_owner_new_role_id UUID REFERENCES organization_roles(id),
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (to_membership_id IS NOT NULL OR to_email IS NOT NULL)
);

CREATE TABLE organization_teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL CHECK (status IN ('active','archived')) DEFAULT 'active',
  created_by_membership_id UUID REFERENCES organization_memberships(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(organization_id, name)
);

CREATE TABLE team_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID NOT NULL REFERENCES organization_teams(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  display_name JSONB NOT NULL,
  permissions JSONB NOT NULL DEFAULT '[]',
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  order_index INTEGER NOT NULL DEFAULT 100,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(team_id, name)
);

CREATE TABLE organization_team_memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID NOT NULL REFERENCES organization_teams(id) ON DELETE CASCADE,
  membership_id UUID NOT NULL REFERENCES organization_memberships(id) ON DELETE CASCADE,
  role_id UUID NOT NULL REFERENCES team_roles(id),
  status TEXT NOT NULL CHECK (status IN ('active','suspended','left')),
  added_by_membership_id UUID REFERENCES organization_memberships(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(team_id, membership_id)
);

CREATE TABLE organization_permission_grants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  subject_type TEXT NOT NULL CHECK (subject_type IN ('membership','team','role')),
  subject_id UUID NOT NULL,
  permission VARCHAR(160) NOT NULL,
  resource_type TEXT,
  resource_id UUID,
  effect TEXT NOT NULL CHECK (effect IN ('allow','deny')),
  granted_by_membership_id UUID REFERENCES organization_memberships(id),
  starts_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_organization_permission_grants_subject
  ON organization_permission_grants(subject_type, subject_id, permission);

CREATE TABLE organization_students (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  last_name TEXT NOT NULL,
  first_name TEXT NOT NULL,
  middle_name TEXT,
  grade INTEGER NOT NULL,
  class_letter TEXT,
  normalized_full_name TEXT NOT NULL,
  status organization_student_status NOT NULL DEFAULT 'active',
  duplicate_note TEXT,
  created_by_membership_id UUID NOT NULL REFERENCES organization_memberships(id),
  updated_by_membership_id UUID REFERENCES organization_memberships(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_organization_students_lookup
  ON organization_students(organization_id, normalized_full_name, grade, class_letter);

CREATE TABLE organization_merges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  primary_organization_id UUID NOT NULL REFERENCES organizations(id),
  duplicate_organization_id UUID NOT NULL REFERENCES organizations(id),
  status organization_merge_status NOT NULL DEFAULT 'planned',
  initiated_by_user_id UUID NOT NULL REFERENCES users(id),
  reason TEXT NOT NULL,
  impact_summary TEXT,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (primary_organization_id <> duplicate_organization_id)
);

organization_roles.permissions, team_roles.permissions and organization_permission_grants.permission store catalog permission keys. CI validates every value against packages/permissions/catalog.ts, and runtime rejects unknown keys. Local namespaces such as org.*, students.*, olympiad.* and problem_bank.* are forbidden.

organization_permission_grants.subject_type + subject_id is polymorphic. Service-layer validation must ensure membership/team/role subjects exist, belong to the same organization context, and are not archived/suspended where that would make the grant invalid.

owner_membership_id cannot be fully enforced as "active organization must have owner" with a simple non-deferrable FK because organization and owner membership are created together. The invariant is enforced by service transaction, deferred constraint or post-transaction validation.

ON DELETE CASCADE for organization children applies only to rare allowed hard delete of never-used draft/test organizations. Normal delete archives the organization and preserves memberships, students, grants and merge history for audit.

Invitations

CREATE TABLE invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status invitation_status NOT NULL DEFAULT 'pending',
  target_type VARCHAR(40) NOT NULL,
  target_id UUID NOT NULL,
  email CITEXT,
  phone VARCHAR(32),
  role_key VARCHAR(80),
  token_hash TEXT NOT NULL UNIQUE,
  invited_by UUID REFERENCES users(id) ON DELETE SET NULL,
  accepted_by UUID REFERENCES users(id) ON DELETE SET NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (email IS NOT NULL OR phone IS NOT NULL),
  CHECK (target_type <> 'organization')
);

CREATE INDEX idx_invitations_target ON invitations(target_type, target_id, status);
CREATE INDEX idx_invitations_expires_at ON invitations(expires_at);

organization_invitations is the canonical model for organization membership invitations. It supports link-only, email, cabinet and SMS delivery. Email is optional because a link invitation or an invitation to an existing user account may not have an email recipient.

invitations remains the generic family/system invitation mechanism and must not be used for organization membership lifecycle. Organization-specific invitations that carry proposed_role_id, invited_by_membership_id, delivery_channel and organization lifecycle status use organization_invitations.

OAuth/OIDC

CREATE TABLE oauth_clients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id VARCHAR(120) NOT NULL UNIQUE,
  client_secret_hash TEXT,
  type oauth_client_type NOT NULL,
  status oauth_client_status NOT NULL DEFAULT 'active',
  name VARCHAR(180) NOT NULL,
  redirect_uris TEXT[] NOT NULL DEFAULT '{}',
  post_logout_redirect_uris TEXT[] NOT NULL DEFAULT '{}',
  allowed_scopes TEXT[] NOT NULL,
  grant_types TEXT[] NOT NULL DEFAULT '{"authorization_code","refresh_token"}',
  is_first_party BOOLEAN NOT NULL DEFAULT FALSE,
  require_consent BOOLEAN NOT NULL DEFAULT TRUE,
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ,
  CHECK (
    (
      type = 'public'
      AND client_secret_hash IS NULL
      AND array_length(redirect_uris, 1) > 0
      AND NOT ('client_credentials' = ANY(grant_types))
    )
    OR
    (
      type = 'confidential'
      AND client_secret_hash IS NOT NULL
    )
  ),
  CHECK (
    (
      'client_credentials' = ANY(grant_types)
      AND type = 'confidential'
      AND array_length(redirect_uris, 1) IS NULL
    )
    OR
    (
      NOT ('client_credentials' = ANY(grant_types))
    )
  )
);

CREATE TABLE oauth_authorization_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code_hash TEXT NOT NULL UNIQUE,
  client_id UUID NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  redirect_uri TEXT NOT NULL,
  scopes TEXT[] NOT NULL,
  code_challenge TEXT NOT NULL,
  code_challenge_method VARCHAR(16) NOT NULL DEFAULT 'S256',
  nonce TEXT,
  expires_at TIMESTAMPTZ NOT NULL,
  consumed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_oauth_codes_expires_at ON oauth_authorization_codes(expires_at);

CREATE TABLE oauth_refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  token_hash TEXT NOT NULL UNIQUE,
  token_family_id UUID NOT NULL,
  replaced_by_token_hash TEXT,
  client_id UUID NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  scopes TEXT[] NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE oauth_consents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id UUID NOT NULL REFERENCES oauth_clients(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  scopes TEXT[] NOT NULL,
  granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ,
  UNIQUE(client_id, user_id)
);

Actor context

CREATE TABLE actor_contexts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  subject_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  subject_student_profile_id UUID REFERENCES student_profiles(id) ON DELETE SET NULL,
  kind actor_context_kind NOT NULL,
  family_group_id UUID REFERENCES family_groups(id) ON DELETE SET NULL,
  organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
  team_id UUID REFERENCES organization_teams(id) ON DELETE SET NULL,
  role_assignment_id UUID REFERENCES user_role_assignments(id) ON DELETE SET NULL,
  oauth_client_id UUID REFERENCES oauth_clients(id) ON DELETE SET NULL,
  delegated_session_id UUID REFERENCES delegated_sessions(id) ON DELETE SET NULL,
  issued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ,
  metadata JSONB NOT NULL DEFAULT '{}',
  CHECK (
    (kind = 'personal' AND subject_user_id IS NULL)
    OR (kind <> 'personal')
  )
);

CREATE INDEX idx_actor_contexts_actor ON actor_contexts(actor_user_id, kind);
CREATE INDEX idx_actor_contexts_subject ON actor_contexts(subject_user_id);
CREATE INDEX idx_actor_contexts_student_profile ON actor_contexts(subject_student_profile_id);

Internal message history and audit

CREATE TABLE message_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id UUID,
  message_kind TEXT CHECK (message_kind IN ('event','command')),
  message_type VARCHAR(120) NOT NULL,
  aggregate_type VARCHAR(80) NOT NULL,
  aggregate_id UUID,
  payload JSONB NOT NULL DEFAULT '{}',
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  request_id UUID
);

CREATE INDEX idx_message_logs_type_time ON message_logs(message_type, occurred_at DESC);

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  subject_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  action VARCHAR(120) NOT NULL,
  resource VARCHAR(120) NOT NULL,
  resource_id UUID,
  status VARCHAR(40) NOT NULL,
  ip_address INET,
  user_agent TEXT,
  request_id UUID,
  details JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_audit_logs_actor_time ON audit_logs(actor_user_id, created_at DESC);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource, resource_id);

Notifications, plugins, system_settings, pages_config and navigation_menus are platform-owned runtime tables. They are not part of the identity database schema; during the bootstrap phase they may be served by identity-api only as a temporary platform host according to platform/topology.md.

Delete policy

Таблица	Политика
`users`	soft delete/anonymization, не hard delete
contacts	cascade от `users`, но после anonymization значения затираются
sessions/codes/refresh tokens	физическая очистка по retention
audit/message logs	retention без cascade от пользователя; PII минимизируется
roles/permissions	system rows не удаляются
organizations/families	архивирование, membership removal; `merged` организации сохраняются физически
OAuth clients	soft delete для истории

Retention

Данные	Срок
`verification_codes`	30 дней после expiry/consume
`auth_flow_sessions`	7 дней
revoked/expired `user_sessions`	180 дней
revoked/expired `refresh_tokens`	180 дней
expired `saved_accounts`, `device_bindings`, `child_device_authorizations`	180 дней
OAuth authorization codes	7 дней
revoked OAuth refresh tokens	180 дней
`message_logs`	365 дней
`audit_logs`	7 лет

message_logs are internal diagnostic/domain-history logs and are not the durable event bus retention store. Event bus retention follows ../../platform/events-bus.md. Audit logs remain 7 years.

Готовность

каждая таблица имеет PK, связи и индексы;
уникальность контактов и external identities не допускает дублей;
deletion/retention policy задана до реализации;
все secrets хранятся как hash или encrypted value;
схема покрывает все сущности из data-model.md.

Зачем нужно​

Общие правила​

Extensions​

Enums​

Users​

Auth​

RBAC​

Family​

Organizations​

Invitations​

OAuth/OIDC​

Actor context​

Internal message history and audit​

Delete policy​

Retention​

Готовность​