schema.sql

-- Patron Supabase schema (fresh setup).
-- ADR-0035: schema.sql odzwierciedla stan po aplikacji wszystkich migracji
-- z `backend/migrations/`. Fresh deployment uruchamia ten plik raz; existing
-- deployment aplikuje przyrostowe migracje przez `npm run migrate`
-- (governance-friendly runner z manualna aplikacja DDL przez Operatora).

create extension if not exists "pgcrypto";

-- ---------------------------------------------------------------------------
-- User profiles
-- ---------------------------------------------------------------------------

create table if not exists public.user_profiles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references auth.users(id) on delete cascade,
  display_name text,
  organisation text,
  tier text not null default 'Free',
  message_credits_used integer not null default 0,
  credits_reset_date timestamptz not null default (now() + interval '30 days'),
  tabular_model text not null default 'gemini-3-flash-preview',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_user_profiles_user
  on public.user_profiles(user_id);

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.user_profiles (user_id)
  values (new.id)
  on conflict (user_id) do nothing;
  return new;
exception when others then
  -- Never block signup if the profile insert fails.
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

create table if not exists public.user_api_keys (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  provider text not null check (provider in ('claude', 'gemini', 'openai')),
  encrypted_key text not null,
  iv text not null,
  auth_tag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique(user_id, provider)
);

create index if not exists idx_user_api_keys_user
  on public.user_api_keys(user_id);

-- ---------------------------------------------------------------------------
-- Projects and documents
-- ---------------------------------------------------------------------------

create table if not exists public.projects (
  id uuid primary key default gen_random_uuid(),
  user_id text not null,
  name text not null,
  cm_number text,
  visibility text not null default 'private',
  shared_with jsonb not null default '[]'::jsonb,
  -- ADR-0067: klasyfikacja danych sprawy (straznik data-residency). Default
  -- fail-closed 'attorney_client_privileged'. Patrz migration 006.
  classification text not null default 'attorney_client_privileged'
    check (classification in ('public','internal','client_general','attorney_client_privileged')),
  -- ADR-0128 (audyt P2 #6): swiadoma zgoda na model chmurowy per-sprawa
  -- (audytowana). Default false (fail-closed). Patrz migration 013.
  cloud_consent boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_projects_user
  on public.projects(user_id);

create index if not exists projects_shared_with_idx
  on public.projects using gin (shared_with);

create table if not exists public.project_subfolders (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects(id) on delete cascade,
  user_id text not null,
  name text not null,
  parent_folder_id uuid references public.project_subfolders(id) on delete cascade,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_project_subfolders_project
  on public.project_subfolders(project_id);

create table if not exists public.documents (
  id uuid primary key default gen_random_uuid(),
  project_id uuid references public.projects(id) on delete cascade,
  user_id text not null,
  filename text not null,
  file_type text,
  size_bytes integer not null default 0,
  page_count integer,
  structure_tree jsonb,
  status text not null default 'pending',
  folder_id uuid references public.project_subfolders(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_documents_user_project
  on public.documents(user_id, project_id);

create index if not exists idx_documents_project_folder
  on public.documents(project_id, folder_id);

create table if not exists public.document_versions (
  id uuid primary key default gen_random_uuid(),
  document_id uuid not null references public.documents(id) on delete cascade,
  storage_path text not null,
  pdf_storage_path text,
  source text not null default 'upload',
  version_number integer,
  display_name text,
  created_at timestamptz not null default now(),
  constraint document_versions_source_check
    check (source = any (array[
      'upload'::text,
      'user_upload'::text,
      'assistant_edit'::text,
      'user_accept'::text,
      'user_reject'::text,
      'generated'::text
    ]))
);

create index if not exists document_versions_document_id_idx
  on public.document_versions(document_id, created_at desc);

create index if not exists document_versions_doc_vnum_idx
  on public.document_versions(document_id, version_number);

alter table public.documents
  add column if not exists current_version_id uuid
  references public.document_versions(id) on delete set null;

-- ADR-0019/0020: status skanu bezpieczenstwa dokumentu wejsciowego.
-- Skan (lib/input-security) badzie wejscie PRZED utrwaleniem/RAG-indeksacja;
-- wynik zapisywany tu + do audit_log (zdarzenie input_security_scan, ADR-0001).
-- 'pending' = jeszcze nieskanowany (kompatybilnosc wstecz z istniejacymi wierszami).
alter table public.documents
  add column if not exists security_status text not null default 'pending'
  check (security_status in
    ('pending', 'allowed', 'quarantined', 'human_review', 'blocked'));

alter table public.documents
  add column if not exists security_report_id text;

create table if not exists public.document_edits (
  id uuid primary key default gen_random_uuid(),
  document_id uuid not null references public.documents(id) on delete cascade,
  chat_message_id uuid,
  version_id uuid not null references public.document_versions(id) on delete cascade,
  change_id text not null,
  del_w_id text,
  ins_w_id text,
  deleted_text text not null default '',
  inserted_text text not null default '',
  context_before text,
  context_after text,
  status text not null default 'pending'
    check (status = any (array[
      'pending'::text,
      'accepted'::text,
      'rejected'::text
    ])),
  created_at timestamptz not null default now(),
  resolved_at timestamptz
);

create index if not exists document_edits_document_id_idx
  on public.document_edits(document_id, created_at desc);

create index if not exists document_edits_message_id_idx
  on public.document_edits(chat_message_id);

create index if not exists document_edits_version_id_idx
  on public.document_edits(version_id);

-- ---------------------------------------------------------------------------
-- Workflows
-- ---------------------------------------------------------------------------

create table if not exists public.workflows (
  id uuid primary key default gen_random_uuid(),
  user_id text,
  title text not null,
  type text not null,
  prompt_md text,
  columns_config jsonb,
  practice text,
  is_system boolean not null default false,
  created_at timestamptz not null default now()
);

create index if not exists idx_workflows_user
  on public.workflows(user_id);

create table if not exists public.hidden_workflows (
  id uuid primary key default gen_random_uuid(),
  user_id text not null,
  workflow_id text not null,
  created_at timestamptz not null default now(),
  unique(user_id, workflow_id)
);

create index if not exists idx_hidden_workflows_user
  on public.hidden_workflows(user_id);

create table if not exists public.workflow_shares (
  id uuid primary key default gen_random_uuid(),
  workflow_id uuid not null references public.workflows(id) on delete cascade,
  shared_by_user_id text not null,
  shared_with_email text not null,
  allow_edit boolean not null default false,
  created_at timestamptz not null default now(),
  constraint workflow_shares_workflow_email_unique
    unique(workflow_id, shared_with_email)
);

create index if not exists workflow_shares_workflow_id_idx
  on public.workflow_shares(workflow_id);

create index if not exists workflow_shares_email_idx
  on public.workflow_shares(shared_with_email);

-- ---------------------------------------------------------------------------
-- Assistant chats
-- ---------------------------------------------------------------------------

create table if not exists public.chats (
  id uuid primary key default gen_random_uuid(),
  project_id uuid references public.projects(id) on delete cascade,
  user_id text not null,
  title text,
  created_at timestamptz not null default now()
);

create index if not exists idx_chats_user
  on public.chats(user_id);

create index if not exists idx_chats_project
  on public.chats(project_id);

create table if not exists public.chat_messages (
  id uuid primary key default gen_random_uuid(),
  chat_id uuid not null references public.chats(id) on delete cascade,
  role text not null,
  content jsonb,
  files jsonb,
  annotations jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_chat_messages_chat
  on public.chat_messages(chat_id);

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'document_edits_chat_message_id_fkey'
      and conrelid = 'public.document_edits'::regclass
  ) then
    alter table public.document_edits
      add constraint document_edits_chat_message_id_fkey
      foreign key (chat_message_id)
      references public.chat_messages(id)
      on delete set null;
  end if;
end;
$$;

-- ---------------------------------------------------------------------------
-- Tabular reviews
-- ---------------------------------------------------------------------------

create table if not exists public.tabular_reviews (
  id uuid primary key default gen_random_uuid(),
  project_id uuid references public.projects(id) on delete cascade,
  user_id text not null,
  title text,
  columns_config jsonb,
  document_ids jsonb,
  workflow_id uuid references public.workflows(id) on delete set null,
  practice text,
  shared_with jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_tabular_reviews_user
  on public.tabular_reviews(user_id);

create index if not exists idx_tabular_reviews_project
  on public.tabular_reviews(project_id);

create index if not exists tabular_reviews_shared_with_idx
  on public.tabular_reviews using gin (shared_with);

create table if not exists public.tabular_cells (
  id uuid primary key default gen_random_uuid(),
  review_id uuid not null references public.tabular_reviews(id) on delete cascade,
  document_id uuid not null references public.documents(id) on delete cascade,
  column_index integer not null,
  content text,
  citations jsonb,
  status text not null default 'pending',
  created_at timestamptz not null default now()
);

create index if not exists idx_tabular_cells_review
  on public.tabular_cells(review_id, document_id, column_index);

create table if not exists public.tabular_review_chats (
  id uuid primary key default gen_random_uuid(),
  review_id uuid not null references public.tabular_reviews(id) on delete cascade,
  user_id text not null,
  title text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists tabular_review_chats_review_idx
  on public.tabular_review_chats(review_id, updated_at desc);

create index if not exists tabular_review_chats_user_idx
  on public.tabular_review_chats(user_id);

create table if not exists public.tabular_review_chat_messages (
  id uuid primary key default gen_random_uuid(),
  chat_id uuid not null references public.tabular_review_chats(id) on delete cascade,
  role text not null,
  content jsonb,
  annotations jsonb,
  created_at timestamptz not null default now()
);

create index if not exists tabular_review_chat_messages_chat_idx
  on public.tabular_review_chat_messages(chat_id, created_at);

-- ---------------------------------------------------------------------------
-- Direct client grant hardening
-- ---------------------------------------------------------------------------
--
-- The frontend uses Supabase directly only for authentication. Application
-- data access goes through the backend API with the service role after the
-- backend verifies the user's JWT. Do not grant the browser anon/authenticated
-- roles direct table privileges for backend-owned data.

revoke all on public.user_profiles from anon, authenticated;
revoke all on public.projects from anon, authenticated;
revoke all on public.project_subfolders from anon, authenticated;
revoke all on public.documents from anon, authenticated;
revoke all on public.document_versions from anon, authenticated;
revoke all on public.document_edits from anon, authenticated;
revoke all on public.workflows from anon, authenticated;
revoke all on public.hidden_workflows from anon, authenticated;
revoke all on public.workflow_shares from anon, authenticated;
revoke all on public.chats from anon, authenticated;
revoke all on public.chat_messages from anon, authenticated;
revoke all on public.tabular_reviews from anon, authenticated;
revoke all on public.tabular_cells from anon, authenticated;
revoke all on public.tabular_review_chats from anon, authenticated;
revoke all on public.tabular_review_chat_messages from anon, authenticated;
revoke all on public.user_api_keys from anon, authenticated;

-- ---------------------------------------------------------------------------
-- Audit trail hash-chain (AI Act art. 12 record-keeping + RODO art. 32)
-- ---------------------------------------------------------------------------
-- Append-only ledger zdarzen istotnych compliance-wise (wiadomosci czatu,
-- wywolania narzedzi MCP, odczyty/edycje dokumentow). Kazdy rekord linkuje
-- hashem do poprzedniego: zmodyfikowanie albo usuniecie srodkowego wpisu
-- psuje lancuch, co weryfikator (CLI scripts/verify-audit-chain.ts) wykryje.
--
-- prev_hash pierwszego (genesis) rekordu = 64 zera "0...0".
-- hash = sha256(prev_hash || canonical_json(payload_for_hash))
--
-- Kolumna payload_for_hash to JSONB z czterech pol:
--   { ts, event_type, actor_user_id, payload }
-- (canonical_json sortuje klucze alfabetycznie, zeby hash byl deterministyczny).

create table if not exists public.audit_log (
  id           bigserial primary key,
  ts           timestamptz not null default now(),
  actor_user_id uuid references auth.users(id) on delete set null,
  event_type   text not null,
  chat_id      uuid references public.chats(id) on delete set null,
  document_id  uuid references public.documents(id) on delete set null,
  payload      jsonb not null,
  prev_hash    text not null,
  hash         text not null unique,
  constraint audit_log_hash_format check (hash ~ '^[0-9a-f]{64}$'),
  constraint audit_log_prev_hash_format check (prev_hash ~ '^[0-9a-f]{64}$'),
  -- ADR-0035: whitelist event_type. Dodawanie nowego event_type wymaga
  -- osobnej migracji + ADR. Lista odzwierciedla migracje
  -- backend/migrations/001_audit_log_event_type_check.sql (7 wartosci)
  -- + 002_audit_log_admin_access_event_types.sql (ADR-0043, +4 wartosci)
  -- + 003_audit_log_event_type_export.sql (ADR-0047, +1 wartosc)
  -- + 004_audit_log_event_type_compute_now.sql (ADR-0048, +1 wartosc)
  -- + 005_audit_log_event_type_llm_route.sql (ADR-0067, +1 wartosc)
  -- + 007_audit_log_event_type_defense_pipeline.sql (ADR-0068, +1 wartosc)
  -- + 008_audit_log_event_type_document_edit.sql (ADR-0070, +1 wartosc)
  -- + 009_audit_log_event_type_tabular_grounding.sql (ADR-0082, +1 wartosc).
  constraint audit_log_event_type_whitelist check (event_type in (
    'chat.message.user',
    'chat.message.assistant',
    'input_security_scan',
    'mcp_security.gateway',
    'ring_policy.decision',
    'rodo.delete',
    'rodo.export',
    'admin.access.audit_viewer',
    'admin.access.audit_export',
    'admin.access.merkle_compute_now',
    'admin.access.security_banner',
    'admin.access.metrics',
    'migrate.rollback',
    'llm_route',
    'defense.pipeline.run',
    'document.edit_resolved',
    'tabular.grounding',
    'project.cloud_consent',
    'connector.toggle'
  ))
);

create index if not exists idx_audit_log_chat
  on public.audit_log(chat_id, ts);
create index if not exists idx_audit_log_actor
  on public.audit_log(actor_user_id, ts);
create index if not exists idx_audit_log_event_type
  on public.audit_log(event_type, ts);

-- RLS: append-only z poziomu service role; uzytkownicy nie czytaja bezposrednio.
alter table public.audit_log enable row level security;
revoke all on public.audit_log from anon, authenticated;

-- Merkle roots nad audit_log (ADR-0026). Warstwa NAD hash-chain (ADR-0001),
-- nie zamiast niego. Daje proof-of-inclusion w O(log n) dla audytora
-- (UODO, rewident kancelarii, biegly w postepowaniu) - bez czytania calego loga.
--
-- Lisce drzewa = audit_log.hash (SHA-256 hex z ADR-0001).
-- Wezly wewnetrzne = SHA-256(left_hex || right_hex), konwencja RFC 6962.
-- Manualny trigger w ADR-0026; auto-hook po N events = ADR-0036.
create table if not exists public.audit_merkle_roots (
  id                 bigserial primary key,
  chain_block_start  bigint not null,
  chain_block_end    bigint not null,
  merkle_root        text not null,
  event_count        int not null,
  computed_at        timestamptz not null default now(),
  computed_by        text not null,
  constraint audit_merkle_roots_block_order
    check (chain_block_start <= chain_block_end),
  constraint audit_merkle_roots_event_count
    check (event_count > 0 and event_count = chain_block_end - chain_block_start + 1),
  constraint audit_merkle_roots_hash_format
    check (merkle_root ~ '^[0-9a-f]{64}$')
);

create index if not exists idx_audit_merkle_roots_block
  on public.audit_merkle_roots(chain_block_start, chain_block_end);
create index if not exists idx_audit_merkle_roots_computed_at
  on public.audit_merkle_roots(computed_at);

-- RLS: tylko service role pisze i czyta. Audytor dostaje proof bundle przez
-- backend (fetchProofForEvent), nie ma bezposredniego dostepu do tabeli.
alter table public.audit_merkle_roots enable row level security;
revoke all on public.audit_merkle_roots from anon, authenticated;

-- ---------------------------------------------------------------------------
-- Schema migrations registry (ADR-0035)
-- ---------------------------------------------------------------------------
-- Rejestr migracji zaaplikowanych przez `npm run migrate:mark`. Operator
-- kancelarii aplikuje DDL manualnie (Supabase SQL Editor / psql / pgAdmin),
-- potem oznacza w rejestrze. Checksum SHA-256 pliku migracji chroni przed
-- silent modyfikacja juz oznaczonej migracji - `npm run migrate:status`
-- pokaze DRIFT.
--
-- Fresh deployment z tego schema.sql dostaje tabele od razu. Existing
-- deployments (pre-ADR-0035) musza wykonac powyzszy CREATE jednorazowo
-- z bootstrap SQL z ADR-0035.

create table if not exists public.schema_migrations (
  id           text primary key,
  name         text not null,
  applied_at   timestamptz not null default now(),
  checksum     text not null,
  constraint schema_migrations_id_format check (id ~ '^\d{3}$'),
  constraint schema_migrations_checksum_format check (checksum ~ '^[0-9a-f]{64}$')
);

alter table public.schema_migrations enable row level security;
revoke all on public.schema_migrations from anon, authenticated;