# SECURITY-POSTURE-SPEC.md
**Contract for `security-posture.html` ↔ Code.** Lane A (Design) owns this spec + the front end; Code owns the gated write RPCs + any crons. Contract-first per the boneyard-collision lesson: **build to this shape, don't redefine it.** Idempotent + reversible (`create … if not exists`, `on conflict do update`).

Project: `mbnakfrkfwxinhxlisyq` · branch `Testin.MikeSupabase` · live at `/security-posture.html` (admin-gated).

---

## 1. Table — `public.pure_security_posture`
The front end reads this table directly via PostgREST: `GET /pure_security_posture?select=*&order=sort.asc`. Read path goes live the moment the table exists + is seeded; writes use the RPCs in §2 (FE has a fail-soft local fallback until they exist).

```sql
create table if not exists public.pure_security_posture (
  id          text primary key,                    -- stable slug, e.g. 'waf'
  cat         text not null default 'app'           -- 'data' | 'edge' | 'app'
                check (cat in ('data','edge','app')),
  status      text not null default 'gap'           -- 'have' | 'partial' | 'gap'
                check (status in ('have','partial','gap')),
  sort        int  not null default 50,             -- display order
  title       text not null,
  imp         text default '',                      -- "our implementation" (may contain <code>…</code>)
  lvl         text default '',                      -- "level up →" text
  human       text default '',                      -- scoped human step; '' = none. Non-empty rows surface in the "Needs a human" panel.
  owner       text default '',                      -- owner hat label: 'Code' | 'Mike' | 'Design' | 'Design+Mike'
  effort      text default 'M' check (effort in ('S','M','L')),
  link        text default '',                      -- control deep-link (relative or https)
  updated_at  timestamptz not null default now(),
  updated_by  text default 'system'
);
```

### RLS
```sql
alter table public.pure_security_posture enable row level security;

-- Read: any authenticated staff (page is already admin-gated client-side; keep server read tight).
create policy secpost_read on public.pure_security_posture
  for select using ( auth.role() = 'authenticated' );
-- (If the board must render pre-auth in preview, add a parallel anon read policy — otherwise omit.)

-- No direct INSERT/UPDATE/DELETE grants — all writes go through the SECURITY DEFINER RPCs below.
```

---

## 2. RPCs (gated, audited)
Mirror the boneyard RPC conventions: `security definer`, admin-check, write an `audit_log` row. The FE calls these via `PureLive.rpc(name, body)`.

| RPC | Args (FE sends) | Effect |
|---|---|---|
| `mcp_secpost_upsert` | `p_id, p_cat, p_status, p_title, p_imp, p_lvl, p_human, p_owner, p_effort, p_link` | `insert … on conflict (id) do update`; set `updated_at=now()`, `updated_by=auth email`. Returns the row. |
| `mcp_secpost_remove` | `p_id` | delete the row (or soft-delete if you prefer a `deleted_at`). Returns `{ok:true}`. |
| `mcp_secpost_advance` | `p_id, p_status, p_note` | set `status=p_status`; append an audit note (who/when/what changed + `p_note`). Returns the row. |
| `mcp_secpost_list` | *(none)* | optional convenience read (FE currently reads the table directly; this is a nice-to-have). |

All four must: (a) verify the caller is a PURE admin/staff hat (reuse the boneyard/`mcp_page_*` admin check); (b) write `audit_log` (`action`, `entity='pure_security_posture'`, `entity_id=p_id`, `actor`, `detail`); (c) be idempotent.

```sql
-- shape only; match your existing helper conventions
create or replace function public.mcp_secpost_upsert(
  p_id text, p_cat text, p_status text, p_title text, p_imp text,
  p_lvl text, p_human text, p_owner text, p_effort text, p_link text)
returns public.pure_security_posture
language plpgsql security definer set search_path = public as $$
declare r public.pure_security_posture;
begin
  -- TODO: admin gate (raise exception if not staff)
  insert into public.pure_security_posture as t
    (id,cat,status,title,imp,lvl,human,owner,effort,link,updated_at,updated_by)
  values (p_id,coalesce(p_cat,'app'),coalesce(p_status,'gap'),p_title,
          coalesce(p_imp,''),coalesce(p_lvl,''),coalesce(p_human,''),
          coalesce(p_owner,''),coalesce(p_effort,'M'),coalesce(p_link,''),
          now(), coalesce(auth.jwt()->>'email','staff'))
  on conflict (id) do update set
    cat=excluded.cat, status=excluded.status, title=excluded.title, imp=excluded.imp,
    lvl=excluded.lvl, human=excluded.human, owner=excluded.owner, effort=excluded.effort,
    link=excluded.link, updated_at=now(), updated_by=excluded.updated_by
  returning * into r;
  -- TODO: insert into audit_log(...)
  return r;
end $$;
```

---

## 3. Seed (the canonical 17 — keep in sync with the FE `SEED[]`)
The FE ships these as a fail-soft floor; insert them so the live table matches and the "Sample" badge clears. `on conflict do nothing` so re-running is safe and never clobbers admin edits.

Areas: **data** (6) · **edge** (5) · **app** (6). Status at authoring: 9 have / 5 partial / 3 gap → **68% coverage**. Human-step rows: `pitr`, `replicas`, `waf`, `bot`.

```sql
insert into public.pure_security_posture (id,cat,status,sort,title,owner,effort,human) values
 ('sql-db','data','have',1,'Serverless SQL database','Code','S',''),
 ('compute','data','have',2,'Serverless compute integration','Code','S',''),
 ('backups','data','have',3,'Backups & snapshots','Code','M',''),
 ('pitr','data','partial',4,'Point-in-time recovery (D1 Time Travel)','Mike','S','Enable Supabase PITR (Database→Backups). Paid add-on (~$100/mo) — confirm spend, then one-click.'),
 ('replicas','data','gap',5,'Global read replication','Mike','S','Add a Supabase Read Replica (Infrastructure→Read Replicas). Paid, region-pick. Low urgency until traffic spreads.'),
 ('tenant','data','partial',6,'Databases-per-tenant scale','Code','M',''),
 ('waf','edge','gap',10,'Web Application Firewall (WAF)','Mike','M','Decide the edge: proxy acquisto.biz DNS through Cloudflare (keeps Netlify hosting, adds WAF). Design preps cutover checklist; Mike flips nameservers.'),
 ('ddos','edge','partial',11,'DDoS protection','Mike','S',''),
 ('bot','edge','gap',12,'Bot management / CAPTCHA','Design+Mike','M','Create a free Turnstile sitekey (dash.cloudflare.com→Turnstile, domain acquisto.biz). Paste sitekey in admin + set Edge secret TURNSTILE_SECRET. Design builds widget + per-surface toggle.'),
 ('edgerl','edge','partial',13,'Edge rate-limiting','Code','M',''),
 ('tls','edge','have',14,'SSL / TLS everywhere','Code','S',''),
 ('rls','app','partial',20,'Row-Level Security (RLS)','Code','M',''),
 ('mfa','app','have',21,'MFA / TOTP + step-up','Code','S',''),
 ('access','app','have',22,'Access rules (IP / region / email)','Code','S',''),
 ('audit','app','have',23,'Audit logging (tamper-evident)','Code','S',''),
 ('secrets','app','have',24,'Secrets management','Code','S',''),
 ('hwkey','app','have',25,'Hardware keys (WebAuthn / FIDO2)','Code','S','')
on conflict (id) do nothing;
```
*(Full `imp`/`lvl` prose lives in the FE seed — Code can copy it in or leave those columns empty; the FE merges live rows over the seed by `id`, so empty live columns fall back to the seed text.)*

---

## 4. Related backend work this board tracks (own tickets)
- **`edgerl`** → enforce `pure_rate_limits` inside `pure-mcp` `chat.send` (per-IP/session) — pairs with the rate-limit admin editor (Lane A building the FE).
- **`bot`** → verify Turnstile token server-side in `pure-mcp` before `chat.send` / sign-in once `TURNSTILE_SECRET` is set.
- **`audit`** → add a periodic `pure_audit_chain` verify to the nightly sweep group.
- **`secrets`** → quarterly key-rotation reminder cron.

## 5. Change log
- 2026-06-30 — Lane A: initial spec + FE live; table greenfield (no collision; `pure_security_posture` did not previously exist). Read path = direct PostgREST; writes = RPCs above (FE fail-soft until built).