Jesse
105 lines
5.1 KiB
SQL
105 lines
5.1 KiB
SQL
-- Personal report persistence for self-hosted PostgreSQL (staging).
|
|
-- Mirrors supabase/migrations/20260806010000_personal_reports.sql
|
|
-- one-to-one in table shape, constraints, RLS and grants.
|
|
--
|
|
-- Ownership model: rows are owned by auth.users(id) (the business-auth
|
|
-- mirror kept in sync by identity.sync_user_to_business_auth). Normal
|
|
-- application sessions connect as app_runtime (member of authenticated) and
|
|
-- set local role authenticated; they may select/delete only their own rows
|
|
-- through RLS plus the explicit owner grants below, and can never
|
|
-- insert/update (generation and status writes are performed exclusively
|
|
-- through service_role, which has BYPASSRLS and full table privileges).
|
|
-- admin_runtime has no direct access to report bodies (least privilege);
|
|
-- server-side generation runs through service_role, which admin_runtime may
|
|
-- SET ROLE to.
|
|
--
|
|
-- Idempotency: unique (user_id, request_id) is the primary lock; replay of a
|
|
-- known requestId requires the same request_fingerprint (a sha256 of the
|
|
-- caller's request intent), so a different payload under the same requestId
|
|
-- surfaces as request_conflict instead of silently overwriting. Failed
|
|
-- retries are not implicitly upserted here; callers either reuse the failed
|
|
-- record via a new requestId or surface the stable failure.
|
|
--
|
|
-- No birth details, report bodies, model prompts or exception stacks are ever
|
|
-- written to logs, index columns or audit events; failure_code is a stable
|
|
-- enum shared with frontend/src/lib/personal-report-service.ts and
|
|
-- scripts/personal_report_contract.py.
|
|
|
|
create table if not exists public.personal_reports (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
session_id uuid,
|
|
chart_profile_id uuid,
|
|
request_id uuid not null,
|
|
request_fingerprint text not null check (request_fingerprint ~ '^[0-9a-f]{64}$'),
|
|
report_type text not null check (report_type in ('personal_full', 'personal_thematic')),
|
|
status text not null check (status in ('generating', 'ready', 'failed')),
|
|
schema_version text not null check (schema_version = 'report_document.v1'),
|
|
presentation_mode text not null check (presentation_mode in ('default', 'research')),
|
|
requested_themes text[] not null default '{}'::text[],
|
|
report_document jsonb,
|
|
calculation_hash text check (calculation_hash is null or calculation_hash ~ '^[0-9a-f]{64}$'),
|
|
evidence_hash text check (evidence_hash is null or evidence_hash ~ '^[0-9a-f]{64}$'),
|
|
skill_source_commit text check (skill_source_commit is null or skill_source_commit ~ '^[0-9a-f]{40}$'),
|
|
skill_snapshot_sha256 text not null check (skill_snapshot_sha256 ~ '^[0-9a-f]{64}$'),
|
|
failure_code text check (failure_code in (
|
|
'profile_incomplete',
|
|
'birth_time_not_usable',
|
|
'report_generation_in_progress',
|
|
'report_rate_limited',
|
|
'calculation_unavailable',
|
|
'model_unavailable',
|
|
'report_schema_invalid',
|
|
'report_guard_rejected',
|
|
'report_not_found'
|
|
)),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
completed_at timestamptz,
|
|
check ((status = 'ready') = (report_document is not null)),
|
|
check ((status = 'ready') = (completed_at is not null)),
|
|
check ((status = 'ready') = (calculation_hash is not null)),
|
|
check ((status = 'ready') = (evidence_hash is not null)),
|
|
check ((status = 'failed') = (failure_code is not null)),
|
|
unique (user_id, request_id)
|
|
);
|
|
|
|
create index if not exists personal_reports_user_created_idx
|
|
on public.personal_reports (user_id, created_at desc);
|
|
|
|
-- One in-flight generation per user, enforced by the database so a second
|
|
-- request cannot start while the first is still generating.
|
|
create unique index if not exists personal_reports_one_generating_per_user
|
|
on public.personal_reports (user_id)
|
|
where status = 'generating';
|
|
|
|
alter table public.personal_reports enable row level security;
|
|
|
|
revoke all on table public.personal_reports from public, anon, authenticated, service_role;
|
|
revoke all on table public.personal_reports from app_runtime, admin_runtime, migration_runner, backup_reader;
|
|
|
|
drop policy if exists personal_reports_select_own on public.personal_reports;
|
|
create policy personal_reports_select_own
|
|
on public.personal_reports
|
|
for select
|
|
to authenticated
|
|
using (auth.uid() = user_id);
|
|
|
|
drop policy if exists personal_reports_delete_own on public.personal_reports;
|
|
create policy personal_reports_delete_own
|
|
on public.personal_reports
|
|
for delete
|
|
to authenticated
|
|
using (auth.uid() = user_id);
|
|
|
|
-- Normal users can never insert or update rows: creating a generating record
|
|
-- and moving it to ready/failed are server-side operations only. RLS
|
|
-- policies alone do not grant table privileges, so the owner read/delete
|
|
-- grants below are required for the policies to be reachable.
|
|
grant select, delete on table public.personal_reports to authenticated;
|
|
|
|
-- admin_runtime intentionally has no direct access to report bodies (least
|
|
-- privilege); server-side generation runs through service_role, which
|
|
-- admin_runtime may SET ROLE to.
|
|
grant select, insert, update, delete on table public.personal_reports to service_role;
|