08 · Evidence Room — Engineering Spec

Use: Internal system. Every claim in every report has a clickable source. If a number can't be linked back, it doesn't get published.

Audience: Engineering (to build), Head of Impact (to own), Account Managers (to use), Assurers (to query).

Goal

A reader looking at a number in any MEM report can:

1. Click the number.
2. Land on the Evidence Room record.
3. See: which dataset rows produced it, which aggregate view, which methodology version, who signed off, when.
4. Reproduce the number from the source.

Architecture

┌────────────────────────────────────────────────────────────┐
│ Report (PDF/web/board pack)                                │
│  └── claim (number, chart, quote)                          │
│       └── evidence_ref: "ER-2026Q1-0142"                   │
└─────────────────────────┬──────────────────────────────────┘
                          │ resolves to
                          ▼
┌────────────────────────────────────────────────────────────┐
│ evidence_records (table)                                   │
│  id, claim_text, claim_type, source_query_hash,            │
│  source_dataset_version, methodology_version,              │
│  confidence_factor, n, signed_off_by, signed_off_at        │
└─────────────────────────┬──────────────────────────────────┘
                          │ links to
                ┌─────────┴─────────┐
                ▼                   ▼
   ┌──────────────────────┐  ┌────────────────────────┐
   │ Data model           │  │ Methodology versions   │
   │ (Sprint 5 / 05)      │  │ (this sprint, file 04) │
   └──────────────────────┘  └────────────────────────┘

Database (Supabase)

Table — evidence_records

RLS

• read: any authenticated user with staff role.
• insert/update: only impact_lead or head_of_impact (use has_role() security-definer function as per project user-roles pattern).
• Service-role writes only via a createServerFn with requireSupabaseAuth + role check; never direct table writes from the client.

Storage

A bucket evidence-room (private) stores:

• Per-engagement working spreadsheets (XLSX).
• Anonymised CSV extracts behind annual reports.
• Signed-off PDFs of each report version.

Path convention: engagements/<client_org_id>/<quarter>/<filename> and annual/<year>/<filename>. Path-based RLS (bucket_id = 'evidence-room' AND auth.uid()::text = (storage.foldername(name))[1]) is insufficient here — use a server function that checks the user's role and the client_org_id explicitly.

Server functions

Implement in src/server/evidence-room.functions.ts (createServerFn with requireSupabaseAuth + role gate).

All write paths must log to an evidence_audit_log append-only table (who, when, what changed, why).

URL pattern (public reports)

Each public-report number renders as a link:

https://memacademy.org/evidence/ER-2026Q1-0142

Routes under src/routes/evidence/$evidenceId.tsx:

• For authenticated staff: full record.
• For everyone else: redacted view (claim, claim_type, n, confidence_factor, methodology_version, signed-off date, "request full audit access" mailto).

Data model integration

Re-uses the Sprint 5 / 05 schema. Important constraints carried forward:

• All aggregates respect the n ≥ 5 view.
• Suppression flag is set in the calculation layer, not at the report layer.
• Dataset versions are snapshot-tagged whenever an evidence record is created — so a re-run of the same SQL months later still produces the same hash if the data hasn't moved.

What this system explicitly prevents

• Publishing a number that has no evidence_record.
• Editing a published record (only corrections allowed).
• Deleting records (insert-only + corrections).
• A non-Impact-Lead user creating sign-offs.
• A claim being linked to a source_query_hash that doesn't reproduce.

Acceptance criteria for the build

• Migrations for evidence_records, evidence_audit_log, and the evidence-room storage bucket.
• RLS policies tested via tests/security-definer-authz.test.ts style pattern.
• Server functions covered by unit tests including the role-gate denial paths.
• Public /evidence/:id route renders both authenticated and redacted views.
• Report-builder tooling will not publish a draft if any claim lacks an evidence_ref.
• Audit log is verifiably append-only.

This is the engineering spec, not the build. When the user says "ship the Evidence Room," this becomes the brief.