1+-- ============================================================================
2+-- TECS REVISED DATABASE SCHEMA
3+-- Texas Executive Clinical Systems
4+-- Purpose: Production-oriented Supabase/PostgreSQL schema incorporating
5+-- corrected RLS, encrypted-content storage patterns, credential
6+-- verification records, append-only incident addenda, and audit logging.
7+-- Author: Manus AI
8+-- Date: 2026-05-20
9+-- ============================================================================
10+
11+-- ---------------------------------------------------------------------------
12+-- 0. EXTENSIONS AND SCHEMA
13+-- ---------------------------------------------------------------------------
14+
15+create extension if not exists pgcrypto;
16+create extension if not exists "uuid-ossp";
17+-- Enable pgaudit from the Supabase dashboard when available, then configure
18+-- role/object logging outside this migration. Some hosted environments restrict
19+-- direct CREATE EXTENSION for pgaudit.
20+-- create extension if not exists pgaudit;
21+
22+create schema if not exists tecs;
23+
24+-- ---------------------------------------------------------------------------
25+-- 1. CONTROLLED VOCABULARIES
26+-- ---------------------------------------------------------------------------
27+
28+do $$ begin
29+ create type tecs.member_role as enum (
30+ text-amber-300">'facility_admin',
31+ text-amber-300">'scheduler',
32+ text-amber-300">'compliance_officer',
33+ text-amber-300">'clinical_reviewer',
34+ text-amber-300">'read_only_auditor'
35+ );
36+exception when duplicate_object then null;
37+end $$;
38+
39+do $$ begin
40+ create type tecs.license_status as enum (
41+ text-amber-300">'active',
42+ text-amber-300">'expired',
43+ text-amber-300">'suspended',
44+ text-amber-300">'encumbered',
45+ text-amber-300">'under_review',
46+ text-amber-300">'unknown'
47+ );
48+exception when duplicate_object then null;
49+end $$;
50+
51+do $$ begin
52+ create type tecs.compliance_status as enum (
53+ text-amber-300">'green',
54+ text-amber-300">'yellow',
55+ text-amber-300">'red',
56+ text-amber-300">'manual_review'
57+ );
58+exception when duplicate_object then null;
59+end $$;
60+
61+do $$ begin
62+ create type tecs.credential_source as enum (
63+ text-amber-300">'nursys_enotify_api',
64+ text-amber-300">'texas_bon_portal_manual',
65+ text-amber-300">'document_upload',
66+ text-amber-300">'facility_attestation',
67+ text-amber-300">'system_backfill'
68+ );
69+exception when duplicate_object then null;
70+end $$;
71+
72+do $$ begin
73+ create type tecs.verification_result as enum (
74+ text-amber-300">'clear',
75+ text-amber-300">'flagged',
76+ text-amber-300">'not_found',
77+ text-amber-300">'source_unavailable',
78+ text-amber-300">'manual_review_required'
79+ );
80+exception when duplicate_object then null;
81+end $$;
82+
83+do $$ begin
84+ create type tecs.shift_status as enum (
85+ text-amber-300">'open',
86+ text-amber-300">'assigned',
87+ text-amber-300">'locked',
88+ text-amber-300">'released',
89+ text-amber-300">'cancelled',
90+ text-amber-300">'completed'
91+ );
92+exception when duplicate_object then null;
93+end $$;
94+
95+do $$ begin
96+ create type tecs.incident_severity as enum (
97+ text-amber-300">'tier_1_critical',
98+ text-amber-300">'tier_2_clinical',
99+ text-amber-300">'tier_3_admin'
100+ );
101+exception when duplicate_object then null;
102+end $$;
103+
104+do $$ begin
105+ create type tecs.audit_action as enum (
106+ text-amber-300">'insert',
107+ text-amber-300">'update',
108+ text-amber-300">'delete_attempt_blocked',
109+ text-amber-300">'credential_scan_started',
110+ text-amber-300">'credential_scan_completed',
111+ text-amber-300">'credential_flag_detected',
112+ text-amber-300">'hard_stop_applied',
113+ text-amber-300">'shift_released',
114+ text-amber-300">'incident_created',
115+ text-amber-300">'incident_addendum_created',
116+ text-amber-300">'legal_hold_applied',
117+ text-amber-300">'security_event'
118+ );
119+exception when duplicate_object then null;
120+end $$;
121+
122+-- ---------------------------------------------------------------------------
123+-- 2. IDENTITY, TENANCY, AND ROLE MEMBERSHIP
124+-- ---------------------------------------------------------------------------
125+
126+create table if not exists tecs.organizations (
127+ id uuid primary key default gen_random_uuid(),
128+ name text not null,
129+ legal_name text,
130+ tax_identifier_last4 text,
131+ business_associate_agreement_on_file boolean not null default false,
132+ is_active boolean not null default true,
133+ created_at timestamptz not null default now(),
134+ updated_at timestamptz not null default now()
135+);
136+
137+create table if not exists tecs.facilities (
138+ id uuid primary key default gen_random_uuid(),
139+ organization_id uuid not null references tecs.organizations(id) on delete restrict,
140+ name text not null,
141+ region text not null default text-amber-300">'DFW',
142+ npi text,
143+ state_license_number text,
144+ address_line1 text,
145+ address_line2 text,
146+ city text,
147+ state text not null default text-amber-300">'TX',
148+ postal_code text,
149+ is_active boolean not null default true,
150+ created_at timestamptz not null default now(),
151+ updated_at timestamptz not null default now(),
152+ unique (organization_id, name)
153+);
154+
155+create table if not exists tecs.user_profiles (
156+ user_id uuid primary key references auth.users(id) on delete cascade,
157+ display_name text not null,
158+ email text,
159+ phone text,
160+ is_platform_admin boolean not null default false,
161+ created_at timestamptz not null default now(),
162+ updated_at timestamptz not null default now()
163+);
164+
165+create table if not exists tecs.facility_memberships (
166+ id uuid primary key default gen_random_uuid(),
167+ facility_id uuid not null references tecs.facilities(id) on delete cascade,
168+ user_id uuid not null references auth.users(id) on delete cascade,
169+ role tecs.member_role not null,
170+ is_active boolean not null default true,
171+ granted_by uuid references auth.users(id) on delete set null,
172+ created_at timestamptz not null default now(),
173+ updated_at timestamptz not null default now(),
174+ unique (facility_id, user_id, role)
175+);
176+
177+create index if not exists idx_facility_memberships_user_facility
178+ on tecs.facility_memberships(user_id, facility_id)
179+ where is_active = true;
180+
181+-- ---------------------------------------------------------------------------
182+-- 3. NURSE MASTER RECORDS AND FACILITY ASSIGNMENTS
183+-- ---------------------------------------------------------------------------
184+
185+create table if not exists tecs.nurses (
186+ id uuid primary key default gen_random_uuid(),
187+ legal_name text not null,
188+ preferred_name text,
189+ date_of_birth_hash text,
190+ license_number text not null,
191+ license_jurisdiction text not null default text-amber-300">'TX',
192+ license_type text not null default text-amber-300">'RN',
193+ ncsbn_id text,
194+ license_status tecs.license_status not null default text-amber-300">'unknown',
195+ compliance_status tecs.compliance_status not null default text-amber-300">'manual_review',
196+ bls_expiration date,
197+ tb_expiration date,
198+ compact_license boolean not null default false,
199+ last_verified_at timestamptz,
200+ next_verification_due_at timestamptz,
201+ created_at timestamptz not null default now(),
202+ updated_at timestamptz not null default now(),
203+ unique (license_jurisdiction, license_number)
204+);
205+
206+create index if not exists idx_nurses_status_due
207+ on tecs.nurses(compliance_status, next_verification_due_at);
208+
209+create table if not exists tecs.facility_nurses (
210+ id uuid primary key default gen_random_uuid(),
211+ facility_id uuid not null references tecs.facilities(id) on delete cascade,
212+ nurse_id uuid not null references tecs.nurses(id) on delete restrict,
213+ relationship_status text not null default text-amber-300">'active',
214+ onboarding_completed_at timestamptz,
215+ offboarded_at timestamptz,
216+ created_at timestamptz not null default now(),
217+ updated_at timestamptz not null default now(),
218+ unique (facility_id, nurse_id)
219+);
220+
221+create index if not exists idx_facility_nurses_nurse
222+ on tecs.facility_nurses(nurse_id);
223+
224+-- ---------------------------------------------------------------------------
225+-- 4. CREDENTIAL DOCUMENTS AND REAL-TIME VERIFICATION RECORDS
226+-- ---------------------------------------------------------------------------
227+
228+create table if not exists tecs.credential_documents (
229+ id uuid primary key default gen_random_uuid(),
230+ nurse_id uuid not null references tecs.nurses(id) on delete cascade,
231+ facility_id uuid references tecs.facilities(id) on delete cascade,
232+ document_type text not null,
233+ storage_bucket text not null,
234+ storage_path text not null,
235+ file_sha256 text not null,
236+ issued_at date,
237+ expires_at date,
238+ verified_by uuid references auth.users(id) on delete set null,
239+ verified_at timestamptz,
240+ created_at timestamptz not null default now(),
241+ unique (storage_bucket, storage_path)
242+);
243+
244+create table if not exists tecs.credential_verifications (
245+ id uuid primary key default gen_random_uuid(),
246+ nurse_id uuid not null references tecs.nurses(id) on delete cascade,
247+ source tecs.credential_source not null,
248+ result tecs.verification_result not null,
249+ license_status tecs.license_status not null default text-amber-300">'unknown',
250+ source_reference_id text,
251+ source_checked_at timestamptz not null default now(),
252+ raw_payload jsonb,
253+ raw_payload_sha256 text,
254+ parsed_payload jsonb,
255+ risk_score integer not null default 0 check (risk_score between 0 and 100),
256+ reason text,
257+ reviewed_by uuid references auth.users(id) on delete set null,
258+ reviewed_at timestamptz,
259+ created_at timestamptz not null default now()
260+);
261+
262+create index if not exists idx_credential_verifications_nurse_created
263+ on tecs.credential_verifications(nurse_id, created_at desc);
264+
265+create index if not exists idx_credential_verifications_result
266+ on tecs.credential_verifications(result, source_checked_at desc);
267+
268+-- ---------------------------------------------------------------------------
269+-- 5. SHIFT DISPATCH CORE
270+-- ---------------------------------------------------------------------------
271+
272+create table if not exists tecs.shifts (
273+ id uuid primary key default gen_random_uuid(),
274+ facility_id uuid not null references tecs.facilities(id) on delete cascade,
275+ nurse_id uuid references tecs.nurses(id) on delete set null,
276+ unit_type text not null,
277+ hourly_rate numeric(8,2) not null check (hourly_rate >= 0),
278+ shift_start timestamptz not null,
279+ shift_end timestamptz not null,
280+ status tecs.shift_status not null default text-amber-300">'open',
281+ locked_at timestamptz,
282+ released_reason text,
283+ created_by uuid references auth.users(id) on delete set null,
284+ created_at timestamptz not null default now(),
285+ updated_at timestamptz not null default now(),
286+ check (shift_end > shift_start),
287+ check ((status in (text-amber-300">'assigned',text-amber-300">'locked',text-amber-300">'completed') and nurse_id is not null) or status in (text-amber-300">'open',text-amber-300">'released',text-amber-300">'cancelled'))
288+);
289+
290+create index if not exists idx_shifts_facility_window
291+ on tecs.shifts(facility_id, shift_start, shift_end);
292+
293+create index if not exists idx_shifts_nurse_future
294+ on tecs.shifts(nurse_id, shift_start)
295+ where nurse_id is not null;
296+
297+-- ---------------------------------------------------------------------------
298+-- 6. ENCRYPTION-READY INCIDENT REPORTS AND ADDENDA
299+-- ---------------------------------------------------------------------------
300+
301+create table if not exists tecs.incident_reports (
302+ id uuid primary key default gen_random_uuid(),
303+ nurse_id uuid not null references tecs.nurses(id) on delete restrict,
304+ facility_id uuid not null references tecs.facilities(id) on delete restrict,
305+ severity_tier tecs.incident_severity not null,
306+ incident_type text not null,
307+ narrative_ciphertext bytea not null,
308+ narrative_sha256 text not null,
309+ encryption_key_ref text not null,
310+ encryption_algorithm text not null default text-amber-300">'app-managed-aead-xchacha20poly1305',
311+ is_legal_hold boolean not null default false,
312+ legal_hold_reason text,
313+ gps_latitude numeric(9,6),
314+ gps_longitude numeric(9,6),
315+ reported_by uuid references auth.users(id) on delete set null,
316+ created_at timestamptz not null default now(),
317+ updated_at timestamptz not null default now()
318+);
319+
320+create index if not exists idx_incident_reports_facility_created
321+ on tecs.incident_reports(facility_id, created_at desc);
322+
323+create index if not exists idx_incident_reports_nurse_created
324+ on tecs.incident_reports(nurse_id, created_at desc);
325+
326+create table if not exists tecs.incident_addenda (
327+ id uuid primary key default gen_random_uuid(),
328+ incident_id uuid not null references tecs.incident_reports(id) on delete restrict,
329+ addendum_ciphertext bytea not null,
330+ addendum_sha256 text not null,
331+ encryption_key_ref text not null,
332+ encryption_algorithm text not null default text-amber-300">'app-managed-aead-xchacha20poly1305',
333+ created_by uuid references auth.users(id) on delete set null,
334+ created_at timestamptz not null default now()
335+);
336+
337+create index if not exists idx_incident_addenda_incident_created
338+ on tecs.incident_addenda(incident_id, created_at asc);
339+
340+-- ---------------------------------------------------------------------------
341+-- 7. APPEND-ONLY AUDIT EVENTS
342+-- ---------------------------------------------------------------------------
343+
344+create table if not exists tecs.audit_events (
345+ id uuid primary key default gen_random_uuid(),
346+ facility_id uuid references tecs.facilities(id) on delete set null,
347+ nurse_id uuid references tecs.nurses(id) on delete set null,
348+ actor_user_id uuid references auth.users(id) on delete set null,
349+ actor_kind text not null default text-amber-300">'system',
350+ action tecs.audit_action not null,
351+ entity_schema text not null default text-amber-300">'tecs',
352+ entity_table text,
353+ entity_id uuid,
354+ reason text,
355+ metadata jsonb not null default text-amber-300">'{}'::jsonb,
356+ source_ip inet,
357+ user_agent text,
358+ request_id text,
359+ created_at timestamptz not null default now()
360+);
361+
362+create index if not exists idx_audit_events_facility_created
363+ on tecs.audit_events(facility_id, created_at desc);
364+
365+create index if not exists idx_audit_events_entity
366+ on tecs.audit_events(entity_table, entity_id, created_at desc);
367+
368+-- ---------------------------------------------------------------------------
369+-- 8. UPDATED_AT AUTOMATION
370+-- ---------------------------------------------------------------------------
371+
372+create or replace function tecs.set_updated_at()
373+returns trigger
374+language plpgsql
375+as $$
376+begin
377+ new.updated_at = now();
378+ return new;
379+end;
380+$$;
381+
382+create trigger trg_organizations_updated_at
383+before update on tecs.organizations
384+for each row execute function tecs.set_updated_at();
385+
386+create trigger trg_facilities_updated_at
387+before update on tecs.facilities
388+for each row execute function tecs.set_updated_at();
389+
390+create trigger trg_user_profiles_updated_at
391+before update on tecs.user_profiles
392+for each row execute function tecs.set_updated_at();
393+
394+create trigger trg_facility_memberships_updated_at
395+before update on tecs.facility_memberships
396+for each row execute function tecs.set_updated_at();
397+
398+create trigger trg_nurses_updated_at
399+before update on tecs.nurses
400+for each row execute function tecs.set_updated_at();
401+
402+create trigger trg_facility_nurses_updated_at
403+before update on tecs.facility_nurses
404+for each row execute function tecs.set_updated_at();
405+
406+create trigger trg_shifts_updated_at
407+before update on tecs.shifts
408+for each row execute function tecs.set_updated_at();
409+
410+create trigger trg_incident_reports_updated_at
411+before update on tecs.incident_reports
412+for each row execute function tecs.set_updated_at();
413+
414+-- ---------------------------------------------------------------------------
415+-- 9. IMMUTABILITY AND HARD-STOP TRIGGERS
416+-- ---------------------------------------------------------------------------
417+
418+create or replace function tecs.block_incident_mutation()
419+returns trigger
420+language plpgsql
421+security definer
422+set search_path = tecs, public
423+as $$
424+begin
425+ if tg_op = text-amber-300">'DELETE' then
426+ insert into tecs.audit_events(action, actor_kind, entity_table, entity_id, facility_id, nurse_id, reason)
427+ values (text-amber-300">'delete_attempt_blocked', text-amber-300">'database_trigger', text-amber-300">'incident_reports', old.id, old.facility_id, old.nurse_id,
428+ text-amber-300">'Attempted deletion of immutable incident report was blocked.');
429+ raise exception text-amber-300">'Audit violation: incident reports are append-only and cannot be deleted.';
430+ end if;
431+
432+ if old.narrative_ciphertext is distinct from new.narrative_ciphertext
433+ or old.narrative_sha256 is distinct from new.narrative_sha256
434+ or old.encryption_key_ref is distinct from new.encryption_key_ref then
435+ raise exception text-amber-300">'Audit violation: incident narratives are immutable. Create an incident_addenda record instead.';
436+ end if;
437+
438+ if old.is_legal_hold = true and new.is_legal_hold = false then
439+ raise exception text-amber-300">'Audit violation: legal holds cannot be removed through standard update paths.';
440+ end if;
441+
442+ return new;
443+end;
444+$$;
445+
446+create trigger trg_block_incident_update
447+before update on tecs.incident_reports
448+for each row execute function tecs.block_incident_mutation();
449+
450+create trigger trg_block_incident_delete
451+before delete on tecs.incident_reports
452+for each row execute function tecs.block_incident_mutation();
453+
454+create or replace function tecs.block_audit_event_mutation()
455+returns trigger
456+language plpgsql
457+as $$
458+begin
459+ raise exception text-amber-300">'Audit violation: audit_events are append-only and cannot be updated or deleted.';
460+end;
461+$$;
462+
463+create trigger trg_block_audit_event_update
464+before update on tecs.audit_events
465+for each row execute function tecs.block_audit_event_mutation();
466+
467+create trigger trg_block_audit_event_delete
468+before delete on tecs.audit_events
469+for each row execute function tecs.block_audit_event_mutation();
470+
471+create or replace function tecs.log_incident_insert()
472+returns trigger
473+language plpgsql
474+security definer
475+set search_path = tecs, public
476+as $$
477+begin
478+ insert into tecs.audit_events(action, actor_kind, actor_user_id, entity_table, entity_id, facility_id, nurse_id, reason)
479+ values (text-amber-300">'incident_created', text-amber-300">'authenticated_user', auth.uid(), text-amber-300">'incident_reports', new.id, new.facility_id, new.nurse_id,
480+ text-amber-300">'Incident report created as immutable encrypted record.');
481+ return new;
482+end;
483+$$;
484+
485+create trigger trg_log_incident_insert
486+after insert on tecs.incident_reports
487+for each row execute function tecs.log_incident_insert();
488+
489+create or replace function tecs.log_incident_addendum_insert()
490+returns trigger
491+language plpgsql
492+security definer
493+set search_path = tecs, public
494+as $$
495+declare
496+ v_facility_id uuid;
497+ v_nurse_id uuid;
498+begin
499+ select facility_id, nurse_id into v_facility_id, v_nurse_id
500+ from tecs.incident_reports
501+ where id = new.incident_id;
502+
503+ insert into tecs.audit_events(action, actor_kind, actor_user_id, entity_table, entity_id, facility_id, nurse_id, reason)
504+ values (text-amber-300">'incident_addendum_created', text-amber-300">'authenticated_user', auth.uid(), text-amber-300">'incident_addenda', new.id, v_facility_id, v_nurse_id,
505+ text-amber-300">'Incident addendum appended without modifying original incident narrative.');
506+ return new;
507+end;
508+$$;
509+
510+create trigger trg_log_incident_addendum_insert
511+after insert on tecs.incident_addenda
512+for each row execute function tecs.log_incident_addendum_insert();
513+
514+-- ---------------------------------------------------------------------------
515+-- 10. RLS HELPER FUNCTIONS
516+-- ---------------------------------------------------------------------------
517+
518+create or replace function tecs.is_platform_admin()
519+returns boolean
520+language sql
521+security definer
522+set search_path = tecs, public
523+stable
524+as $$
525+ select exists (
526+ select 1
527+ from tecs.user_profiles up
528+ where up.user_id = auth.uid()
529+ and up.is_platform_admin = true
530+ );
531+$$;
532+
533+create or replace function tecs.is_facility_member(p_facility_id uuid)
534+returns boolean
535+language sql
536+security definer
537+set search_path = tecs, public
538+stable
539+as $$
540+ select auth.uid() is not null and (
541+ tecs.is_platform_admin()
542+ or exists (
543+ select 1
544+ from tecs.facility_memberships fm
545+ where fm.facility_id = p_facility_id
546+ and fm.user_id = auth.uid()
547+ and fm.is_active = true
548+ )
549+ );
550+$$;
551+
552+create or replace function tecs.has_facility_role(p_facility_id uuid, p_roles tecs.member_role[])
553+returns boolean
554+language sql
555+security definer
556+set search_path = tecs, public
557+stable
558+as $$
559+ select auth.uid() is not null and (
560+ tecs.is_platform_admin()
561+ or exists (
562+ select 1
563+ from tecs.facility_memberships fm
564+ where fm.facility_id = p_facility_id
565+ and fm.user_id = auth.uid()
566+ and fm.role = any(p_roles)
567+ and fm.is_active = true
568+ )
569+ );
570+$$;
571+
572+create or replace function tecs.can_access_nurse(p_nurse_id uuid)
573+returns boolean
574+language sql
575+security definer
576+set search_path = tecs, public
577+stable
578+as $$
579+ select auth.uid() is not null and (
580+ tecs.is_platform_admin()
581+ or exists (
582+ select 1
583+ from tecs.facility_nurses fn
584+ join tecs.facility_memberships fm on fm.facility_id = fn.facility_id
585+ where fn.nurse_id = p_nurse_id
586+ and fm.user_id = auth.uid()
587+ and fm.is_active = true
588+ and fn.relationship_status = text-amber-300">'active'
589+ )
590+ );
591+$$;
592+
593+-- ---------------------------------------------------------------------------
594+-- 11. ROW LEVEL SECURITY
595+-- ---------------------------------------------------------------------------
596+
597+alter table tecs.organizations enable row level security;
598+alter table tecs.facilities enable row level security;
599+alter table tecs.user_profiles enable row level security;
600+alter table tecs.facility_memberships enable row level security;
601+alter table tecs.nurses enable row level security;
602+alter table tecs.facility_nurses enable row level security;
603+alter table tecs.credential_documents enable row level security;
604+alter table tecs.credential_verifications enable row level security;
605+alter table tecs.shifts enable row level security;
606+alter table tecs.incident_reports enable row level security;
607+alter table tecs.incident_addenda enable row level security;
608+alter table tecs.audit_events enable row level security;
609+
610+-- Organizations
611+create policy "organization members can read their organizations"
612+on tecs.organizations for select
613+using (
614+ tecs.is_platform_admin()
615+ or exists (
616+ select 1
617+ from tecs.facilities f
618+ join tecs.facility_memberships fm on fm.facility_id = f.id
619+ where f.organization_id = organizations.id
620+ and fm.user_id = auth.uid()
621+ and fm.is_active = true
622+ )
623+);
624+
625+create policy "platform admins can manage organizations"
626+on tecs.organizations for all
627+using (tecs.is_platform_admin())
628+with check (tecs.is_platform_admin());
629+
630+-- Facilities
631+create policy "facility members can read facilities"
632+on tecs.facilities for select
633+using (tecs.is_facility_member(id));
634+
635+create policy "facility admins can update their facilities"
636+on tecs.facilities for update
637+using (tecs.has_facility_role(id, array[text-amber-300">'facility_admin']::tecs.member_role[]))
638+with check (tecs.has_facility_role(id, array[text-amber-300">'facility_admin']::tecs.member_role[]));
639+
640+-- User profiles
641+create policy "users can read own profile"
642+on tecs.user_profiles for select
643+using (auth.uid() is not null and user_id = auth.uid());
644+
645+create policy "users can update own limited profile"
646+on tecs.user_profiles for update
647+using (auth.uid() is not null and user_id = auth.uid())
648+with check (auth.uid() is not null and user_id = auth.uid() and is_platform_admin = false);
649+
650+create policy "platform admins can read profiles"
651+on tecs.user_profiles for select
652+using (tecs.is_platform_admin());
653+
654+-- Facility memberships
655+create policy "facility admins can read memberships"
656+on tecs.facility_memberships for select
657+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]));
658+
659+create policy "facility admins can manage memberships"
660+on tecs.facility_memberships for all
661+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin']::tecs.member_role[]))
662+with check (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin']::tecs.member_role[]));
663+
664+-- Nurses
665+create policy "facility members can read linked nurses"
666+on tecs.nurses for select
667+using (tecs.can_access_nurse(id));
668+
669+create policy "compliance officers can manage linked nurses"
670+on tecs.nurses for update
671+using (
672+ tecs.is_platform_admin()
673+ or exists (
674+ select 1
675+ from tecs.facility_nurses fn
676+ where fn.nurse_id = nurses.id
677+ and tecs.has_facility_role(fn.facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[])
678+ )
679+)
680+with check (
681+ tecs.is_platform_admin()
682+ or exists (
683+ select 1
684+ from tecs.facility_nurses fn
685+ where fn.nurse_id = nurses.id
686+ and tecs.has_facility_role(fn.facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[])
687+ )
688+);
689+
690+-- Facility-nurse assignments
691+create policy "facility members can read facility nurse assignments"
692+on tecs.facility_nurses for select
693+using (tecs.is_facility_member(facility_id));
694+
695+create policy "facility compliance can manage nurse assignments"
696+on tecs.facility_nurses for all
697+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]))
698+with check (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]));
699+
700+-- Credential documents
701+create policy "facility compliance can read credential documents"
702+on tecs.credential_documents for select
703+using (
704+ tecs.is_platform_admin()
705+ or (facility_id is not null and tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]))
706+ or tecs.can_access_nurse(nurse_id)
707+);
708+
709+create policy "facility compliance can manage credential documents"
710+on tecs.credential_documents for all
711+using (tecs.is_platform_admin() or (facility_id is not null and tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[])))
712+with check (tecs.is_platform_admin() or (facility_id is not null and tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[])));
713+
714+-- Credential verifications
715+create policy "facility compliance can read verification history"
716+on tecs.credential_verifications for select
717+using (tecs.is_platform_admin() or tecs.can_access_nurse(nurse_id));
718+
719+-- Writes to credential_verifications should normally be performed by service_role
720+-- from Edge Functions or back-office jobs. Human users may only update review fields
721+-- through controlled RPC functions, not direct table updates.
722+
723+-- Shifts
724+create policy "facility members can read shifts"
725+on tecs.shifts for select
726+using (tecs.is_facility_member(facility_id));
727+
728+create policy "schedulers can manage shifts"
729+on tecs.shifts for all
730+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'scheduler',text-amber-300">'compliance_officer']::tecs.member_role[]))
731+with check (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'scheduler',text-amber-300">'compliance_officer']::tecs.member_role[]));
732+
733+-- Incident reports
734+create policy "clinical and compliance roles can read incident metadata"
735+on tecs.incident_reports for select
736+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer',text-amber-300">'clinical_reviewer',text-amber-300">'read_only_auditor']::tecs.member_role[]));
737+
738+create policy "clinical and compliance roles can create incident reports"
739+on tecs.incident_reports for insert
740+with check (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer',text-amber-300">'clinical_reviewer']::tecs.member_role[]));
741+
742+create policy "compliance officers can update legal hold metadata only"
743+on tecs.incident_reports for update
744+using (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]))
745+with check (tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer']::tecs.member_role[]));
746+
747+-- Incident addenda
748+create policy "incident readers can read addenda"
749+on tecs.incident_addenda for select
750+using (
751+ exists (
752+ select 1
753+ from tecs.incident_reports ir
754+ where ir.id = incident_addenda.incident_id
755+ and tecs.has_facility_role(ir.facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer',text-amber-300">'clinical_reviewer',text-amber-300">'read_only_auditor']::tecs.member_role[])
756+ )
757+);
758+
759+create policy "clinical and compliance roles can append addenda"
760+on tecs.incident_addenda for insert
761+with check (
762+ exists (
763+ select 1
764+ from tecs.incident_reports ir
765+ where ir.id = incident_addenda.incident_id
766+ and tecs.has_facility_role(ir.facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer',text-amber-300">'clinical_reviewer']::tecs.member_role[])
767+ )
768+);
769+
770+-- Audit events
771+create policy "facility auditors can read audit events"
772+on tecs.audit_events for select
773+using (
774+ tecs.is_platform_admin()
775+ or (facility_id is not null and tecs.has_facility_role(facility_id, array[text-amber-300">'facility_admin',text-amber-300">'compliance_officer',text-amber-300">'read_only_auditor']::tecs.member_role[]))
776+);
777+
778+-- audit_events inserts are generated by database triggers and service_role jobs.
779+-- No direct authenticated insert/update/delete policy is intentionally provided.
780+
781+-- ---------------------------------------------------------------------------
782+-- 12. HARD-STOP PROCEDURE FOR CREDENTIAL FAILURES
783+-- ---------------------------------------------------------------------------
784+
785+create or replace function tecs.apply_credential_hard_stop(
786+ p_nurse_id uuid,
787+ p_reason text,
788+ p_verification_id uuid default null
789+)
790+returns void
791+language plpgsql
792+security definer
793+set search_path = tecs, public
794+as $$
795+declare
796+ v_shift record;
797+begin
798+ update tecs.nurses
799+ set compliance_status = text-amber-300">'red',
800+ license_status = case when license_status = text-amber-300">'active' then text-amber-300">'under_review' else license_status end,
801+ updated_at = now()
802+ where id = p_nurse_id;
803+
804+ for v_shift in
805+ select id, facility_id
806+ from tecs.shifts
807+ where nurse_id = p_nurse_id
808+ and shift_start > now()
809+ and status in (text-amber-300">'assigned', text-amber-300">'locked')
810+ loop
811+ update tecs.shifts
812+ set nurse_id = null,
813+ status = text-amber-300">'released',
814+ locked_at = null,
815+ released_reason = p_reason,
816+ updated_at = now()
817+ where id = v_shift.id;
818+
819+ insert into tecs.audit_events(facility_id, nurse_id, actor_kind, action, entity_table, entity_id, reason, metadata)
820+ values (
821+ v_shift.facility_id,
822+ p_nurse_id,
823+ text-amber-300">'system',
824+ text-amber-300">'shift_released',
825+ text-amber-300">'shifts',
826+ v_shift.id,
827+ p_reason,
828+ jsonb_build_object(text-amber-300">'verification_id', p_verification_id)
829+ );
830+ end loop;
831+
832+ insert into tecs.audit_events(nurse_id, actor_kind, action, entity_table, entity_id, reason, metadata)
833+ values (
834+ p_nurse_id,
835+ text-amber-300">'system',
836+ text-amber-300">'hard_stop_applied',
837+ text-amber-300">'nurses',
838+ p_nurse_id,
839+ p_reason,
840+ jsonb_build_object(text-amber-300">'verification_id', p_verification_id)
841+ );
842+end;
843+$$;
844+
845+-- ---------------------------------------------------------------------------
846+-- 13. OPERATIONAL VIEWS
847+-- ---------------------------------------------------------------------------
848+
849+create or replace view tecs.v_facility_shift_roster as
850+select
851+ s.id as shift_id,
852+ s.facility_id,
853+ f.name as facility_name,
854+ s.unit_type,
855+ s.shift_start,
856+ s.shift_end,
857+ s.status,
858+ n.id as nurse_id,
859+ n.legal_name as nurse_name,
860+ n.license_number,
861+ n.license_status,
862+ n.compliance_status,
863+ n.last_verified_at
864+from tecs.shifts s
865+join tecs.facilities f on f.id = s.facility_id
866+left join tecs.nurses n on n.id = s.nurse_id;
867+
868+-- RLS on underlying tables governs this view. In Supabase, prefer security_invoker
869+-- views where supported by the platform version:
870+-- alter view tecs.v_facility_shift_roster set (security_invoker = true);
871+
872+-- ---------------------------------------------------------------------------
873+-- 14. RECOMMENDED GRANTS
874+-- ---------------------------------------------------------------------------
875+
876+-- Adapt grants to the schemas exposed through Supabase API settings.
877+grant usage on schema tecs to authenticated;
878+grant select, insert, update, delete on all tables in schema tecs to authenticated;
879+grant usage, select on all sequences in schema tecs to authenticated;
880+
881+-- Do not grant anon access to TECS clinical tables unless a public endpoint is
882+-- explicitly designed and independently reviewed.
883+
884+-- ============================================================================
885+-- END OF TECS REVISED DATABASE SCHEMA
886+-- ============================================================================
887+
