Security baked into the schema.
We treat security posture as an architectural commitment, not a checkbox. Here's the substance — no sales fluff.
Row-Level Security on every table
Every SOSPRO-owned table has Postgres RLS policies that scope reads and writes to the caller's organisation_id. Cross-org access is impossible at the database layer, not the application layer. Server-side queries also pin organization_id explicitly — belt + suspenders.
Storage by signed URL
Patient documents live in private Supabase Storage buckets, partitioned by organisation_id. The browser uploads to a one-shot signed URL and downloads through short-lived (60 second) signed reads. There are no public file URLs — RLS on storage.objects enforces ownership at every operation.
Auth by Supabase
Email/password auth via Supabase Auth. Sessions use httpOnly cookies — no tokens in localStorage where XSS could grab them. Password reset uses Supabase's signed recovery link flow with a dedicated reset page that signs the user out after the new password is set.
Audit columns everywhere
created_by_user_id, archived_by_user_id, recorded_by_user_id, last_status_change_by_user_id — every mutable row carries who-did-what timestamps. Beds even auto-stamp last_status_change_at via trigger when the status flips, so the audit captures the truth even if metadata edits happen in the same transaction.
Structured workflow audit log
Admit, discharge, transfer-status transitions, case status / priority changes — every meaningful workflow event writes a typed entry to case_activity_log with the actor, timestamp, and structured details (which bed, what priority, from/to state). The clinic / transport timeline you see in the dashboard is the same record an auditor or insurer would read.
Smart Insights is opt-in and PHI-aware
Smart Insights is off by default — flip it on per-org in Settings. When enabled, we send a structured summary (module configuration, counts, last 20 audit-log actions with their actor names) to a third-party analysis service — NOT raw patient records, free-form notes, or document contents. Our vendor's terms forbid using customer traffic to train models. Turn Smart Insights off and the call stops immediately; the heuristic suggestions resume.
Process & guardrails
Migrations are reviewed
Every schema change ships as a named Supabase migration. We never run ad-hoc DDL against production. Migrations are documented in source control and visible to SOSCOMMAND under the handshake protocol.
Provider RLS is additive
When SOSPRO needs new write access (e.g. invoices, agreements, beds), we add provider-side RLS policies as additive rules — leaving SOSCOMMAND's existing internal_ops policies untouched. No silent expansions of access.
E-signature audit
Consent / financial / AOB signatures capture typed name, server-side timestamp, IP address, and user agent. The audit metadata can't be forged from the client. Once a form is active, it can't be re-signed; only terminated with reason.
Frozen on handoff
Once Tourist SOS Operations attaches a case_id to an invoice, SOSPRO writes are frozen at the database trigger — even if a clinic user tries to record a payment, the trigger no-ops the update so ops can own the lifecycle.
What we deliberately don't store
Honesty about what's outside our threat model.
- Plaintext passwords (Supabase handles auth hashing)
- Credit card numbers (we don't process card data; that flows through your existing terminal)
- Full social security / national ID numbers in patient records
- Anything in our application logs that ties a record to a person