Security architecture

1. Three trust levels, one door each

Every request that reaches taxpayer data arrives through exactly one of three identities. Nothing else can read the database.

• Anonymous visitor — publishable key only. Sees marketing pages and public tools. No policy grants it access to any taxpayer table.
• Signed-in taxpayer — the browser client carries the user's access token; row-level security scopes every query to auth.uid().
• Signed-in staff — the same browser client, but role rows in user_roles unlock staff policies through the has_role / is_staff helpers.
• Server-only service identity — used exclusively inside server functions for encryption, decryption and privileged writes. It never reaches the browser bundle.

2. Row-level security is the boundary, not the UI

Hiding a button is not a control. Each table enables RLS and only grants the roles its policies actually allow.

• Taxpayer-owned tables (tax_returns, documents, intake_answers, portal_drafts …) carry policies scoped to auth.uid() for read, insert, update and delete.
• Staff tables are gated by public.is_staff(auth.uid()) or a finer capability check via public.has_capability().
• Roles live in a dedicated user_roles table, never on the profile row, so a compromised profile update cannot escalate privileges.
• Grants are issued per role: browser roles get nothing on server-only tables, so even a policy mistake cannot open them.
• Returns in the recycle bin (deleted_at set) are filtered out of client policies until they are restored or purged.

-- shape used across taxpayer tables
alter table public.documents enable row level security;

create policy "owners read their documents"
  on public.documents for select to authenticated
  using (auth.uid() = user_id);

create policy "staff read all documents"
  on public.documents for select to authenticated
  using (public.is_staff(auth.uid()));

3. SECURITY DEFINER paths and why they exist

A handful of lookups must read across ownership lines — a role check inside a policy would recurse, and household identifiers must be masked before they leave the database. Those run as SECURITY DEFINER functions with a pinned search_path.

• public.has_role(), public.is_staff(), public.has_capability() — read user_roles without triggering the policy that itself calls them. They accept a user id and a role, return a boolean, and leak nothing else.
• public.staff_get_spouse() / public.staff_get_dependents() — take a single return id, require auth.uid() to be present, and return rows only when the caller is staff or the owner of that return.
• Those two functions never emit ciphertext for an unfiled return: the encrypted columns are replaced with NULL and a tax_id_hidden flag is returned instead, so last-4 stays invisible until the return is filed.
• Every definer function sets search_path = public, so a hostile schema on the caller's path cannot shadow a table or operator.
• Definer functions are input-limited to scalars (a uuid, an enum) — none of them accept SQL fragments, table names or arbitrary filters.

create or replace function public.staff_get_spouse(_return_id uuid)
returns table (...)
language sql stable security definer
set search_path = public
as $$
  select s.id, s.first_name, ...
         case when public.return_is_filed(s.return_id)
              then s.tax_id_ciphertext else null end,
         not public.return_is_filed(s.return_id) as tax_id_hidden
  from public.return_spouse s
  where _return_id is not null
    and auth.uid() is not null
    and s.return_id = _return_id
    and (
      public.is_staff(auth.uid())
      or exists (select 1 from public.tax_returns r
                 where r.id = _return_id and r.user_id = auth.uid()
                   and r.deleted_at is null)
    )
$$;

4. The encrypted client_ssn vault

Social Security and ITIN numbers are never stored in a readable column and are never present in a browser payload in full.

• Values are encrypted with AES-256-GCM before they touch the database. The row holds ciphertext, an initialisation vector and an authentication tag — nothing else.
• The key lives only in the server secret store as SSN_ENCRYPTION_KEY. It is read inside server function handlers, never at module scope, and never shipped to the client bundle.
• client_ssn carries no policy for anonymous, taxpayer or staff roles, and the browser roles hold no grant on it. Reads happen only through server functions running as the service identity.
• Server functions authorise the caller first (ownership through an RLS-scoped query, or a staff capability check) and only then decrypt.
• What comes back to the screen is the last four digits, and only after the taxpayer passes the one-time-code gate; staff see a masked placeholder until the return is filed.
• Writes follow the same path in reverse: plain digits are validated, encrypted server side, and the plaintext is discarded from memory with the request.

taxpayer browser
   │  useServerFn(getMySsnLast4)   ← no SSN in the request
   ▼
server function (requireSupabaseAuth)
   │  1. verify caller identity (bearer token → auth.uid())
   │  2. verify ownership via RLS-scoped query
   │  3. audit the attempt (actor, request id, outcome)
   ▼
identifiers.server.ts  →  AES-256-GCM decrypt with SSN_ENCRYPTION_KEY
   ▼
client_ssn (ciphertext, iv, auth_tag)   ← service identity only

5. Every identifier touch is audited and alerted

Reads and writes of an identifier are recorded whether they succeed or fail, which is what makes an attack visible rather than silent.

• access_audit_log records the actor id, subject taxpayer, action, resource, IP, user agent, a per-request correlation id and a database timestamp.
• The correlation id is taken from the incoming request headers when present, otherwise generated, so one id ties the browser call, the audit row and the staff alert together.
• A denied or errored identifier attempt raises an immediate staff alert carrying the request id and the caller's role, so staff see the attempt on their dashboard without reading logs.
• The audit table is append-only from the application's point of view: no policy grants update or delete to browser roles.
• Sensitive values are redacted before they are logged; the trail records that an attempt happened, never the number itself.

6. Document storage

Uploads live in a private bucket whose object policies mirror the database rules.

• The bucket is private — there are no public URLs. Downloads are served through short-lived signed URLs issued to an authorised caller.
• Object policies key on the first path segment: a taxpayer may read, insert, update and delete only under a folder named with their own user id.
• Update is policed separately from insert, so an attacker cannot overwrite another taxpayer's file or its metadata row by re-uploading over the same path.
• Metadata rows in documents are protected by their own RLS policies and by triggers that enforce file type, size and the submission lock.
• Staff can read taxpayer folders for preparation work; anonymous callers can do nothing at all.

7. Proven by tests on every change

The rules above are asserted automatically, so a policy regression fails the build rather than reaching production.

• A role harness provisions throwaway anonymous, taxpayer and staff sessions against the real backend and exercises the policies the way a browser would.
• The RLS suite covers the client_ssn vault, rate-limit telemetry, the definer household lookups and the storage bucket.
• A tamper suite uploads a document as one taxpayer and then attempts to overwrite the bytes and the metadata row as another; both must fail and the original must survive.
• A storage lifecycle suite walks upload, re-version and delete for all three roles and asserts the outcome of each combination.
• Continuous integration runs those suites plus dependency, secret and platform security scans on every pull request, and blocks the merge on a new critical finding.