Схема БД

Правила

• Все таблицы используют uuid primary key.
• Все бизнес-таблицы имеют created_at, updated_at.
• Ссылки на identity не объявляются FK, если таблицы живут в разных сервисах.
• JSONB используется для сегментов, правил видимости, источников и конфигурации виджетов.
• Таблицы с финансовыми, персональными или restricted-метриками должны проверяться через permission layer.
• Удаление по умолчанию логическое: archived или deleted_at, кроме append-only history.

Sensitivity enforcement:

• public → management.dashboards.read;
• internal → management.dashboards.read;
• financial → management.metrics.read.financial;
• personal → management.metrics.read.personal;
• restricted → management.metrics.read.restricted.

Dashboard effective sensitivity вычисляется как максимум sensitivity всех metric/drilldown источников. Widget наследует максимальный sensitivity от metric_id и всех drilldown-метрик. Публикация dashboard запрещена, если visibility_rule.requiredPermissions не покрывает effective sensitivity каждого widget.

Enum-значения

create type management_metric_status as enum ('draft','active','deprecated','archived');
create type management_dashboard_status as enum ('draft','review','published','archived');
create type management_plan_status as enum ('draft','review','approved','active','closed','archived','cancelled');
create type management_task_status as enum ('todo','in_progress','blocked','review','done','cancelled');
create type management_alert_status as enum ('open','acknowledged','resolved','dismissed');
create type management_quality_status as enum ('complete','partial','stale','failed','unknown');
create type management_sensitivity_level as enum ('public','internal','financial','personal','restricted');
create type management_goal_profile_status as enum ('draft','active','paused','archived','cancelled');
create type management_goal_status as enum ('draft','active','paused','completed','archived','cancelled');
create type management_goal_source as enum ('family','student','teacher','advisor','manager','system_hypothesis','import');
create type management_recommendation_status as enum ('draft','published','accepted','dismissed','expired');
create type management_diagnostic_status as enum ('scheduled','in_progress','completed','cancelled','failed');

Метрики

create table management_metrics (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title text not null,
  description text,
  owner text not null,
  source_domain text not null,
  formula text not null,
  grain text not null check (grain in ('day','week','month','quarter','year')),
  unit text not null check (unit in ('count','rub','percent','minutes','score','ratio')),
  sensitivity_level management_sensitivity_level not null default 'internal',
  status management_metric_status not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table management_metric_versions (
  id uuid primary key default gen_random_uuid(),
  metric_id uuid not null references management_metrics(id),
  version int not null,
  formula text not null,
  source_contract jsonb not null default '{}'::jsonb,
  valid_from timestamptz not null,
  valid_to timestamptz,
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  unique (metric_id, version)
);

create table management_metric_values (
  id uuid primary key default gen_random_uuid(),
  metric_id uuid not null references management_metrics(id),
  metric_version_id uuid not null references management_metric_versions(id),
  period_start date not null,
  period_end date not null,
  segment jsonb not null default '{}'::jsonb,
  value numeric(18,4) not null,
  quality_status management_quality_status not null default 'unknown',
  calculated_at timestamptz not null,
  ingestion_run_id uuid,
  created_at timestamptz not null default now(),
  unique (metric_id, metric_version_id, period_start, period_end, segment)
);

create index management_metric_values_metric_period_idx
  on management_metric_values (metric_id, period_start, period_end);

create index management_metric_values_segment_gin
  on management_metric_values using gin (segment);

Dashboards

create table management_dashboards (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  description text,
  audience text not null check (audience in ('executive','product','operations','finance','education','custom')),
  status management_dashboard_status not null default 'draft',
  default_period text not null default 'month',
  visibility_rule jsonb not null default '{}'::jsonb,
  created_by_user_id uuid,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table management_dashboard_widgets (
  id uuid primary key default gen_random_uuid(),
  dashboard_id uuid not null references management_dashboards(id) on delete cascade,
  type text not null check (type in ('kpi','line_chart','bar_chart','table','funnel','alert_list','markdown')),
  title text not null,
  metric_id uuid references management_metrics(id),
  position int not null,
  config jsonb not null default '{}'::jsonb,
  drilldown jsonb not null default '{}'::jsonb,
  effective_sensitivity_level management_sensitivity_level not null default 'internal',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (dashboard_id, position)
);

create table management_saved_views (
  id uuid primary key default gen_random_uuid(),
  dashboard_id uuid not null references management_dashboards(id) on delete cascade,
  owner_user_id uuid not null,
  title text not null,
  filters jsonb not null default '{}'::jsonb,
  is_default boolean not null default false,
  created_at timestamptz not null default now()
);

Goals, recommendations and diagnostics

create table goal_profiles (
  id uuid primary key default gen_random_uuid(),
  owner_type text not null check (owner_type in ('student','family','group','organization')),
  owner_id text not null,
  status management_goal_profile_status not null default 'draft',
  context jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table goals (
  id uuid primary key default gen_random_uuid(),
  goal_profile_id uuid not null references goal_profiles(id),
  title text not null,
  type text not null,
  priority text not null default 'normal',
  status management_goal_status not null default 'draft',
  target_date date,
  source management_goal_source not null default 'advisor',
  is_system_hypothesis boolean not null default false,
  activated_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table goal_milestones (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  title text not null,
  target_value jsonb,
  due_at timestamptz,
  status text not null default 'todo',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table goal_owners (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  owner_user_id uuid,
  owner_role text not null,
  created_at timestamptz not null default now()
);

create table goal_subjects (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  source_domain text not null,
  source_type text not null,
  source_id text not null,
  created_at timestamptz not null default now()
);

create table goal_criteria (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  metric_key text,
  operator text not null check (operator in ('gte','lte','eq','range','custom')),
  target_value jsonb,
  evidence_policy jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table goal_evidence (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  source_domain text not null,
  source_type text not null,
  source_id text not null,
  weight numeric(5,2),
  captured_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table goal_reviews (
  id uuid primary key default gen_random_uuid(),
  goal_id uuid not null references goals(id) on delete cascade,
  reviewer_user_id uuid,
  decision text not null,
  comment text,
  reviewed_at timestamptz not null default now()
);

create table recommendations (
  id uuid primary key default gen_random_uuid(),
  goal_profile_id uuid references goal_profiles(id),
  goal_id uuid references goals(id),
  type text not null,
  status management_recommendation_status not null default 'draft',
  reason text,
  confidence numeric(5,2),
  source_model text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table recommendation_actions (
  id uuid primary key default gen_random_uuid(),
  recommendation_id uuid not null references recommendations(id) on delete cascade,
  action_type text not null,
  payload jsonb not null default '{}'::jsonb,
  status text not null default 'pending',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table diagnostic_sessions (
  id uuid primary key default gen_random_uuid(),
  goal_profile_id uuid references goal_profiles(id),
  student_profile_id uuid,
  source_domain text,
  status management_diagnostic_status not null default 'scheduled',
  scheduled_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table diagnostic_results (
  id uuid primary key default gen_random_uuid(),
  diagnostic_session_id uuid not null references diagnostic_sessions(id),
  summary text,
  scores jsonb not null default '{}'::jsonb,
  strengths jsonb not null default '[]'::jsonb,
  risks jsonb not null default '[]'::jsonb,
  recommended_goal_refs jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

Планирование

create table management_plans (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  type text not null check (type in ('sales','learning','operations','finance','staffing','product_launch')),
  owner_user_id uuid not null,
  period_start date not null,
  period_end date not null,
  scope jsonb not null default '{}'::jsonb,
  status management_plan_status not null default 'draft',
  approved_by_user_id uuid,
  approved_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (period_end >= period_start)
);

create table management_plan_targets (
  id uuid primary key default gen_random_uuid(),
  plan_id uuid not null references management_plans(id) on delete cascade,
  metric_id uuid not null references management_metrics(id),
  target_value numeric(18,4) not null,
  comparison_operator text not null check (comparison_operator in ('gte','lte','eq','range')),
  range_min numeric(18,4),
  range_max numeric(18,4),
  segment jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table management_plan_facts (
  id uuid primary key default gen_random_uuid(),
  target_id uuid not null references management_plan_targets(id) on delete cascade,
  metric_value_id uuid references management_metric_values(id),
  fact_value numeric(18,4),
  variance numeric(18,4),
  status text not null check (status in ('on_track','at_risk','off_track','unknown')),
  calculated_at timestamptz not null,
  unique (target_id, calculated_at)
);

Задачи

create table management_tasks (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  status management_task_status not null default 'todo',
  priority text not null default 'normal' check (priority in ('low','normal','high','urgent')),
  assignee_user_id uuid,
  reporter_user_id uuid,
  due_at timestamptz,
  source_domain text,
  source_type text,
  source_id text,
  deduplication_key text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz
);

create unique index management_tasks_deduplication_key_uidx
  on management_tasks (deduplication_key)
  where deduplication_key is not null;

create index management_tasks_assignee_status_idx
  on management_tasks (assignee_user_id, status, due_at);

create table management_task_comments (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references management_tasks(id) on delete cascade,
  author_user_id uuid not null,
  body text not null,
  created_at timestamptz not null default now(),
  deleted_at timestamptz
);

create table management_task_history (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references management_tasks(id) on delete cascade,
  actor_user_id uuid,
  change_type text not null,
  before jsonb,
  after jsonb,
  created_at timestamptz not null default now()
);

create table management_task_templates (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title_template text not null,
  description_template text,
  default_priority text not null default 'normal',
  default_assignee_rule jsonb not null default '{}'::jsonb,
  status text not null check (status in ('active','archived')),
  created_at timestamptz not null default now()
);

Alerts и качество данных

create table management_alert_rules (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  metric_id uuid not null references management_metrics(id),
  condition jsonb not null,
  severity text not null check (severity in ('info','warning','critical')),
  deduplication_key_template text not null,
  task_template_id uuid references management_task_templates(id),
  status text not null check (status in ('draft','active','paused','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table management_alerts (
  id uuid primary key default gen_random_uuid(),
  rule_id uuid not null references management_alert_rules(id),
  status management_alert_status not null default 'open',
  severity text not null check (severity in ('info','warning','critical')),
  deduplication_key text not null,
  payload jsonb not null default '{}'::jsonb,
  task_id uuid references management_tasks(id),
  opened_at timestamptz not null default now(),
  acknowledged_by_user_id uuid,
  acknowledged_at timestamptz,
  resolved_at timestamptz,
  unique (deduplication_key)
);

create table management_data_sources (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  source_domain text not null,
  source_type text not null check (source_type in ('event_stream','snapshot_api','read_model','manual_import')),
  contract jsonb not null default '{}'::jsonb,
  status text not null check (status in ('active','paused','deprecated')),
  created_at timestamptz not null default now()
);

create table management_ingestion_runs (
  id uuid primary key default gen_random_uuid(),
  data_source_id uuid not null references management_data_sources(id),
  status text not null check (status in ('running','succeeded','failed','partial')),
  started_at timestamptz not null,
  finished_at timestamptz,
  rows_read int not null default 0,
  rows_written int not null default 0,
  error_message text
);

create table management_data_quality_issues (
  id uuid primary key default gen_random_uuid(),
  data_source_id uuid references management_data_sources(id),
  ingestion_run_id uuid references management_ingestion_runs(id),
  severity text not null check (severity in ('info','warning','critical')),
  status text not null check (status in ('open','acknowledged','resolved','dismissed')),
  title text not null,
  details jsonb not null default '{}'::jsonb,
  opened_at timestamptz not null default now(),
  resolved_at timestamptz
);

Management actions

create table management_actions (
  id uuid primary key default gen_random_uuid(),
  action_type text not null,
  source_type text,
  source_id uuid,
  status text not null default 'requested' check (status in ('requested','validated','executed','completed','rejected','failed','retried')),
  requested_by_user_id uuid,
  completed_at timestamptz,
  retry_count int not null default 0,
  retry_reason text,
  payload jsonb not null default '{}'::jsonb,
  result jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Audit

create table management_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 management_audit_logs_entity_idx
  on management_audit_logs (entity_type, entity_id, created_at desc);