4a0e043a6d
- supabase_client.py: lazy singleton client (no-ops when env vars absent)
- persistence.py: persist_upload writes batch, source_files, normalized_records,
mapping_decisions, report_runs; persist_export records export_files
- schema.sql: 11-table schema with RLS + WORM rules for audit/raw tables
- main.py: wire persist_upload/persist_export; add ExportRequest body model
so export accepts {records, batch_id}; batch_id returned on upload response
- api.js: add exportFromBackend helper passing batch_id through
- requirements.txt: add supabase>=2.0.0
- smoke_test.py: update export call to new body format
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
175 lines
8.1 KiB
SQL
175 lines
8.1 KiB
SQL
-- Signal Phase 2 Schema
|
|
-- Run this in the Supabase SQL editor (Dashboard > SQL Editor > New query)
|
|
-- Safe to run multiple times — uses IF NOT EXISTS throughout
|
|
|
|
create extension if not exists "uuid-ossp";
|
|
|
|
-- Organizations (DME supplier accounts)
|
|
create table if not exists organizations (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
name text not null,
|
|
slug text unique not null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Users (staff at each org)
|
|
create table if not exists users (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
org_id uuid not null references organizations(id) on delete cascade,
|
|
email text not null unique,
|
|
role text not null default 'staff', -- 'admin' | 'staff'
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Upload batches (one per CSV upload event)
|
|
create table if not exists upload_batches (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
org_id uuid not null references organizations(id) on delete cascade,
|
|
uploaded_by uuid references users(id),
|
|
filename text not null,
|
|
row_count int not null default 0,
|
|
skipped_count int not null default 0,
|
|
status text not null default 'processing', -- 'processing' | 'complete' | 'failed'
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Source files (raw CSV metadata, WORM)
|
|
create table if not exists source_files (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
batch_id uuid not null references upload_batches(id) on delete cascade,
|
|
filename text not null,
|
|
content_hash text not null, -- SHA-256 of file bytes
|
|
byte_size int not null,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Raw rows (original CSV row data before normalization, WORM)
|
|
create table if not exists raw_rows (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
batch_id uuid not null references upload_batches(id) on delete cascade,
|
|
row_number int not null,
|
|
raw_data jsonb not null, -- original column key/value pairs
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Normalized records (scored output)
|
|
create table if not exists normalized_records (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
batch_id uuid not null references upload_batches(id) on delete cascade,
|
|
patient_id_hash text not null, -- SHA-256 of patient_id — no raw PHI stored
|
|
device_type text not null,
|
|
shipment_date date not null,
|
|
quantity int not null default 1,
|
|
payer text not null,
|
|
component text not null default 'sensor',
|
|
coverage_status text not null, -- OUT_OF_COVERAGE | VISIT_DUE | REFILL_WINDOW | OK
|
|
days_remaining int,
|
|
reason text,
|
|
recommended_action text,
|
|
rule_version text not null,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Mapping decisions (header-to-field mapping log per batch)
|
|
create table if not exists mapping_decisions (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
batch_id uuid not null references upload_batches(id) on delete cascade,
|
|
raw_header text not null,
|
|
canonical_field text,
|
|
confidence text not null, -- 'high' | 'inferred' | 'unmapped'
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Report runs (one per scored batch delivered to user)
|
|
create table if not exists report_runs (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
batch_id uuid not null references upload_batches(id) on delete cascade,
|
|
org_id uuid not null references organizations(id) on delete cascade,
|
|
generated_by uuid references users(id),
|
|
status text not null default 'complete',
|
|
total_records int not null default 0,
|
|
flagged_count int not null default 0,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Report items (one row per patient record in the worklist)
|
|
create table if not exists report_items (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
report_run_id uuid not null references report_runs(id) on delete cascade,
|
|
normalized_record_id uuid not null references normalized_records(id),
|
|
patient_id_hash text not null,
|
|
status text not null,
|
|
days_remaining int,
|
|
reason text,
|
|
recommended_action text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Export files (downloaded work queue CSVs)
|
|
create table if not exists export_files (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
report_run_id uuid not null references report_runs(id) on delete cascade,
|
|
exported_by uuid references users(id),
|
|
filename text not null,
|
|
row_count int not null default 0,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Audit events (WORM — append only, never update or delete)
|
|
create table if not exists audit_events (
|
|
id uuid primary key default uuid_generate_v4(),
|
|
org_id uuid references organizations(id),
|
|
user_id uuid references users(id),
|
|
action text not null, -- 'upload' | 'export' | 'login' | 'view_report'
|
|
resource_type text,
|
|
resource_id uuid,
|
|
patient_id_hash text,
|
|
metadata jsonb,
|
|
ip_address text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Enable Row Level Security on all tables
|
|
alter table organizations enable row level security;
|
|
alter table users enable row level security;
|
|
alter table upload_batches enable row level security;
|
|
alter table source_files enable row level security;
|
|
alter table raw_rows enable row level security;
|
|
alter table normalized_records enable row level security;
|
|
alter table mapping_decisions enable row level security;
|
|
alter table report_runs enable row level security;
|
|
alter table report_items enable row level security;
|
|
alter table export_files enable row level security;
|
|
alter table audit_events enable row level security;
|
|
|
|
-- WORM rules: audit_events, raw_rows, source_files are append-only
|
|
do $$ begin
|
|
if not exists (select 1 from pg_rules where rulename = 'audit_events_no_update') then
|
|
create rule audit_events_no_update as on update to audit_events do instead nothing;
|
|
end if;
|
|
if not exists (select 1 from pg_rules where rulename = 'audit_events_no_delete') then
|
|
create rule audit_events_no_delete as on delete to audit_events do instead nothing;
|
|
end if;
|
|
if not exists (select 1 from pg_rules where rulename = 'raw_rows_no_update') then
|
|
create rule raw_rows_no_update as on update to raw_rows do instead nothing;
|
|
end if;
|
|
if not exists (select 1 from pg_rules where rulename = 'raw_rows_no_delete') then
|
|
create rule raw_rows_no_delete as on delete to raw_rows do instead nothing;
|
|
end if;
|
|
if not exists (select 1 from pg_rules where rulename = 'source_files_no_update') then
|
|
create rule source_files_no_update as on update to source_files do instead nothing;
|
|
end if;
|
|
if not exists (select 1 from pg_rules where rulename = 'source_files_no_delete') then
|
|
create rule source_files_no_delete as on delete to source_files do instead nothing;
|
|
end if;
|
|
end $$;
|
|
|
|
-- Indexes
|
|
create index if not exists idx_upload_batches_org on upload_batches(org_id);
|
|
create index if not exists idx_normalized_records_batch on normalized_records(batch_id);
|
|
create index if not exists idx_normalized_records_status on normalized_records(coverage_status);
|
|
create index if not exists idx_audit_events_org on audit_events(org_id);
|
|
create index if not exists idx_audit_events_action on audit_events(action);
|
|
create index if not exists idx_report_items_run on report_items(report_run_id);
|