Security & data protection — requirements

Epic — release gate. MVP-1 requirements for the security & data protection posture (see the feature architecture). These are pass/fail launch conditions, audited each release and sampled continuously in production. Distinct from COPPA (consent) and GDPR (rights) — this is the infrastructure and data-access security layer.

Type	Non-functional
Layer (build approach)	Data — RLS + encryption + key rotation
RICE	R 10 × I 2 × C 80% / E 3 = 5.3 · Tier MVP-1
KPI (summary)	Zero RLS-bypass defects; crash-free sessions ≥ 99.5%; rotation cadence met — every release
Traces to	feature security-data-protection · C4 infrastructure
Depends on	Personas / authz · Account · Offline sync

Success criteria (definitive KPI)

Success = zero RLS-bypass / cross-household-leak defects in testing AND in prod audit; crash-free sessions ≥ 99.5%; secret-rotation cadence met — every release.

• Isolation: zero cross-household data leaks — confirmed by the SP2/SP3 smoke suite and RLS policy regression tests run each release. A single bypass is a release blocker; no ramp-in permitted.
• No service-role key: zero occurrences of the service-role key in client bundles, build artifacts, or checked-in config — confirmed by CI secret scan on every commit.
• Encryption: all data in transit over TLS 1.2+; Supabase server-side encryption at rest confirmed; child PII fields in the local Drift cache encrypted before write.
• Consent gate: zero child member rows written without a valid consentRef — confirmed by DB assertion each release (cross-cutting with children's privacy).
• Ledger integrity: zero UPDATE or DELETE statements succeed against ledger rows via the anon key; zero debits below zero-floor succeed.
• Rotation: anon key and all secrets rotated and logged before every production release; rotation log retained without PII.
• Crash-free sessions: ≥ 99.5% rolling 7-day (Sentry); security-triggered exceptions (token expiry, RLS rejection) handled gracefully — not left as crashes.

Measurement & audit

Instrument so every gate is provable. No child PII in any audit payload; child references are opaque IDs where required.

Check	When	Evidence
RLS policy regression suite	Every release (CI)	Pass on all tables; SP2/SP3 cross-household smoke = 0 rows
CI secret scan (no service-role key)	Every commit	0 pattern matches in bundle + source tree
Consent gate DB assertion	Every release	SELECT count(*) FROM members WHERE kind='child' AND consent_ref IS NULL = 0
Ledger DML policy check	Every release (CI)	UPDATE/DELETE against ledger with anon key returns 403/RLS error
Rotation log entry	Every release	Key name, rotated_at timestamp — no PII
TLS enforcement verification	Quarterly or on infra change	No cleartext connections in network capture
Sentry crash-free session rate	Continuous	≥ 99.5% rolling 7-day

Scope

Supabase RLS is the primary isolation boundary: every table is household-scoped via member_household_ids() / parental_household_ids() keyed to auth.uid(). This epic covers the remaining posture — encryption at rest and in transit (child data especially), secret/key rotation cadence, Storage ACLs for photo submissions, and two cross-cutting integrity invariants:

• Consent gate: no child PII persists without a consentRef — a COPPA control and a security control; enforced in the SDK service layer and the schema.
• Append-only ledger + zero-floor: ledger rows are never mutated; balance is a projection; debit below zero is rejected at service and SQL trigger level. These are integrity controls, not just business rules.

The app uses the anon/publishable key only — the service-role key never appears in client code, bundles, or device-accessible config. Privileged operations go through Supabase Edge Functions (server-side). This is a verifiable architectural invariant: ClientConfig(api: ApiConfig(...)) accepts only the publishable key.

Zero-trust posture: any authenticated user can access exactly the households they belong to — nothing more, nothing less — and only via RLS-enforced policies.

Non-functional requirements

NFR-SEC-1 — Household-scoped RLS: zero cross-household access

Priority: P1 · Status: ✅ policies built → 🔨 regression test coverage required Statement. Every Supabase table row is accessible only to users whose auth.uid() resolves to a member of that row's household via member_household_ids() or parental_household_ids(). No query returns another household's data regardless of client-supplied predicates. Acceptance — Given an authenticated user in household A When they issue any query (direct Supabase or SDK) Then zero rows from household B are returned, and no error response leaks household B identifiers.

NFR-SEC-2 — Anon/publishable key only; service-role key banned from client

Priority: P1 · Status: 🔨 CI gate to enforce Statement. The app and client_sdk are constructed with only the anon/publishable key. The service-role key is never embedded in client bundles, checked-in env files, build artifacts, or any code path reachable from a device. Acceptance — Given a CI secret scan of the full build artifact and source tree When the scan completes Then zero matches for the service-role key pattern; any match fails the pipeline and blocks release.

NFR-SEC-3 — Encryption at rest and in transit

Priority: P1 · Status: 🔨 to verify + local layer to build Statement. All data in transit uses TLS 1.2+ (Supabase-enforced). Data at rest uses Supabase server-side AES-256 encryption. Child PII fields (display name, age band) in the local Drift cache are encrypted at the field or database level before write so device compromise does not expose plaintext child data. Acceptance — Given the app communicating with Supabase When network traffic is captured Then no plaintext connection is established. Given the device's local Drift database file When inspected offline Then child PII fields are not readable as plaintext without the device key.

NFR-SEC-4 — Storage ACLs: photo submissions household-scoped

Priority: P1 · Status: 🔨 to build Statement. The Supabase Storage bucket for chore photo submissions is protected by an RLS policy matching member_household_ids(): only household members may read or write their household's prefix. Signed URL expiry is ≤ 15 minutes. Acceptance — Given a signed URL for a photo belonging to household A When a user authenticated to household B (or unauthenticated) attempts access Then the response is 403. Given a URL past its expiry Then access is denied regardless of the requester's household.

NFR-SEC-5 — Secret/key rotation cadence

Priority: P1 · Status: 🔨 to build (process + CI gate) Statement. The anon key and any other production secrets are rotated before every production release. Rotation is logged (key name, rotated_at timestamp — no PII). A CI gate blocks the release if the last rotation pre-dates the current release cycle. Acceptance — Given a production release build When the release checklist executes Then a rotation log entry exists for the current cycle; the CI gate passes. Given the log Then it contains no PII or secret material — only key names and timestamps.

NFR-SEC-6 — Consent gate as security control (no child PII without consentRef)

Priority: P1 · Status: 🔨 to build (cross-ref COPPA) Statement. The SDK service layer refuses to write any child member record without a valid consentRef. A database NOT NULL constraint and RLS policy are the backstop so the control cannot be bypassed by a direct anon-key insert. Acceptance — Given a direct Supabase insert of a child member row without a consentRef using the anon key When the insert is attempted Then the database rejects it. Given ChildService.create() called before consent is captured Then the service throws before any network call is made.

NFR-SEC-7 — Append-only ledger + zero-floor as integrity controls

Priority: P1 · Status: 🔨 to build (SQL trigger partial) Statement. Token-economy ledger rows are never updated or deleted; balance is always a SUM projection. A SQL trigger and service-layer guard enforce the zero-floor: a debit that would produce a negative balance is rejected. These controls are enforced at the schema boundary, not only in the SDK. Acceptance — Given an UPDATE or DELETE against a ledger row via the anon key When the statement executes Then the database rejects it (RLS DML policy USING (false)). Given a debit that would produce balance < 0 When the service processes it Then it throws InsufficientFundsException and no ledger row is written.

Architecture considerations

• RLS is the hard boundary. member_household_ids() and parental_household_ids() are the sole access grants. Every table must have both SELECT and DML policies; gaps are the primary risk surface. Policy regression tests must cover every table, not just the core entities.
• No service-role key in the app is an architectural invariant enforced in ClientConfig. Privileged operations (e.g., cross-household admin, consent cascade deletes) go through Supabase Edge Functions where the service-role key lives server-side and is never exposed to the client.
• Encryption layers stack: TLS (transport, Supabase-enforced) → server-side AES-256 (Supabase at rest) → optional Drift field-level encryption (local cache, child PII only). The local layer matters because device compromise is a realistic threat for a family app with child data.
• Storage ACLs mirror household RLS: the bucket prefix is {household_id}/{member_id}/... and the Storage RLS policy reuses member_household_ids(). Signed URL expiry caps the exposure window for any leaked URL.
• Consent and ledger invariants are schema-enforced: NOT NULL on consent_ref, a BEFORE INSERT OR UPDATE OR DELETE trigger on the ledger, and RLS DML policies set to USING (false) for UPDATE/DELETE. The SDK service layer is the first enforcement point; the schema is the backstop that survives any SDK bypass.
• Session expiry / token lifecycle: Supabase JWTs expire (default 1 h). The app must handle AuthException without crashing — clear the session, surface a re-auth prompt. This is both a security control and a crash-free-sessions requirement.
• Privacy model (three buckets) governs telemetry exports: OPERATIONAL (child PII → Supabase/VPC, never in telemetry) · INTERNAL BI (no child identity, content-free, never marketing) · MARKETING (parent/account only: count, plan type, parent-feature-active; never child-derived). The security layer enforces that no telemetry export path carries a child identifier.

Design work (ahead of build)

Primarily backend and infra — UI surface is minimal:

• Session-expiry UX state: the experience when the Supabase JWT expires mid-session (re-auth prompt, not an unhandled crash or blank screen); must be specified and implemented before launch.
• RLS rejection error handling: graceful error state when a request is rejected at the database boundary (e.g., household context mismatch); error messages must not expose internal identifiers or household IDs.
• Rotation runbook: a documented (non-UI) step-by-step checklist for the secret rotation process, executable by any team member without institutional knowledge.

Decisions (resolved for MVP-1)

Resolved — see the MVP-1 decisions log for the canonical record, rationale, and status legend (✅ decided · ⚖️ counsel confirms · 🔜 MVP-1.x).

• D-SEC-1 — Local encryption. ✅ SQLCipher via the Drift SQLCipher wrapper (whole-DB encryption at rest) for MVP-1; SDK-level field encryption deferred (granularity not yet needed). Locked before the offline-cache milestone.
• D-SEC-2 — Photo E2E. ✅ Supabase server-side Storage encryption is sufficient for MVP-1 (not client-side E2E) — content moderation must inspect photos server-side. Documented risk acceptance; revisit if moderation moves client-side.
• D-SEC-3 — Pen test. ✅ Third-party pen test at the private-beta → public-launch boundary (before public launch); internal review continuous. Feeds the SOC 2 roadmap.
• D-SEC-4 — SOC 2 Type II threshold. ⚖️ Trigger formal attestation at the first B2B / school ask or ~10k MAU, whichever first (placeholder); maintain evidence-collecting practices until then. Confirm with counsel/sales.
• D-SEC-5 — Anon-key rotation. ✅ CI-enforced reminder (a scheduled check flagging key age) for MVP-1 — between manual-only and full automation; automate via the Supabase dashboard API later. Locked before first production release.

Out of scope (MVP-1)

• SOC 2 Type II audit and formal attestation → post-MVP, threshold-gated.
• External pen test by a third-party firm → scheduled separately; findings feed a future security epic.
• GDPR data-subject rights (erasure / portability SLAs) → Privacy & GDPR (pre-EU launch).
• General admin audit-log UI surface → Audit log (the ledger and consent row are in scope; the browsable admin view is not).
• Client-side zero-knowledge architecture or client-side key derivation → deferred per the post-MVP architecture decisions; server-side encryption is the MVP-1 model.
• End-to-end encrypted photo storage → pending content-moderation design; flagged as an open question in the feature architecture.