apps/backend/src/infrastructure/database/migrations/0077_synthetic_operations_persistence.sqlSQL
create table if not exists synthetic_tenant_manifests (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
tenant_id text not null,
template text not null,
seed bigint not null,
resources jsonb not null,
external_adapters text not null default 'simulation-only',
data_origin text not null default 'synthetic',
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint synthetic_tenant_manifest_origin_check check (data_origin='synthetic'),
constraint synthetic_tenant_manifest_adapter_check check (external_adapters='simulation-only'),
constraint synthetic_tenant_manifest_seed_check check (seed>=0),
constraint synthetic_tenant_manifest_id_check check (tenant_id ~* '^synthetic[-_][a-z0-9._-]+$'),
constraint synthetic_tenant_manifest_unique unique (organization_id,tenant_id)
);
create index if not exists idx_synthetic_tenant_manifests_org_updated
on synthetic_tenant_manifests(organization_id,updated_at desc);
create table if not exists synthetic_run_evidence (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
tenant_id text not null,
scenario_id text not null,
scenario_version text not null,
status text not null,
summary_safe_json jsonb not null,
recorded_by_user_id uuid references user_accounts(id) on delete set null,
recorded_at timestamptz not null default now(),
constraint synthetic_run_evidence_status_check check (status in ('passed','failed')),
constraint synthetic_run_evidence_tenant_id_check check (tenant_id ~* '^synthetic[-_][a-z0-9._-]+$'),
constraint synthetic_run_evidence_tenant_fk foreign key (organization_id,tenant_id)
references synthetic_tenant_manifests(organization_id,tenant_id) on delete cascade
);
create index if not exists idx_synthetic_run_evidence_org_time
on synthetic_run_evidence(organization_id,recorded_at desc);
create index if not exists idx_synthetic_run_evidence_tenant_time
on synthetic_run_evidence(organization_id,tenant_id,recorded_at desc);