roadmap(data): complete PostgreSQL RLS rollout by access family

[tiankaima]

Context

#550 completed the Prisma/Hyperdrive pilot and production activation for the first pure-owner families. This follow-up tracks the remaining business tables without treating policy count as the goal.

Already enforced in production with ENABLE + FORCE ROW LEVEL SECURITY:

• Todo
• DashboardLinkClick
• DashboardLinkPin
• BusUserPreference
• Upload, UploadPending (owner isolation + definer-read/cleanup worker policies; Upload lifecycle: coordinate PUT, completion, and R2 cleanup with per-key leases #571 / feat: explicit subscriptions, upload leases, and dashboard overview #702)
• HomeworkCompletion (owner isolation + profile aggregate definer; refactor(db): route personal preferences through RLS context #599)
• CommentReaction (owner isolation + summary definer; feat(db): activate personal preference RLS #600)
• UserSectionSubscription (owner isolation + public count definer; feat(db): make user section subscriptions explicit before RLS activation #604 / feat: explicit subscriptions, upload leases, and dashboard overview #702)
• UserUstcIdentity (owner isolation; feat(auth): collect USTC OIDC gid/sno identities #701)

The runtime pattern is a short interactive Prisma transaction, transaction-local set_config('app.user_id', ..., true), and all protected queries on the exact transaction client. CI uses a dedicated LOGIN NOINHERIT NOBYPASSRLS role.

Remaining access families

• Collaborative content (Comment, Homework, Description, attachments): define anonymous, authenticated, soft-banned, moderator, and admin matrices before policies.
• Better Auth tables: use a separate auth-service database identity because many queries happen before an end-user identity exists.
• Catalog/importer tables: enforce role grants for read-only runtime/importer ownership; avoid meaningless USING (true) policies.

Hyperdrive / operations

• Provision a least-privilege production runtime role with NOBYPASSRLS; application owner/migration credentials remain separate.
• Bind auth-sensitive traffic to a cache-disabled Hyperdrive configuration. Hyperdrive currently caches only queries whose functions are immutable, so current_setting policy reads should not be cacheable, but the explicit binding avoids relying on that implementation detail.
• Never use session-scoped SET with transaction pooling; identity must be transaction-local.

Acceptance criteria

• Inventory every business table and assign one access family plus owner/service/migration roles.
• Add raw SQL migrations with both USING and WITH CHECK where applicable (completed families above).
• Route every protected access path through the correct transaction/service context before activation (uploads, subscriptions, homework completion, reactions).
• Add default-deny, cross-user concurrency, forged-owner, delete/cascade, and connection-reset tests per family (upload/subscription/homework/reaction integration suites).
• Add public-read regression tests for uploads and contribution aggregates.
• Provision and verify production runtime/auth/importer roles and Hyperdrive bindings.
• Roll out family by family with migration and rollback notes (uploads, subscriptions, homework completion, reactions, USTC identity).
• Complete collaborative content and catalog/importer verification; keep Better Auth on auth runtime only.

References: #550, #596, #598, #599, #600, #604, #701, #702.

[tiankaima]

2026-07-22 follow-up audit

Further RLS activation is blocked by #603: the production Hyperdrive configuration currently reports origin user postgres with caching enabled. The database role attributes still need an origin-side audit, but production must move to a cache-disabled, least-privilege NOBYPASSRLS runtime identity before another policy family is enabled.

The next pure-owner family is also missing from the original inventory: Prisma's implicit _UserCalendarSections join stores user section subscriptions. Its services currently use nested connect/disconnect and getPrisma(locale) outside withUserDbContext. The safe sequence is to make this join an explicit UserSectionSubscription(userId, sectionId) model, route reads/writes through an exact transaction client, migrate data, test parity, and only then activate owner RLS.

Additional rollout gates found:

• run representative REST/GraphQL/MCP services under the runtime role, not only direct delegate tests; current bus preference service also reads BusCampus, which the CI runtime role does not grant;
• add pg_catalog policy/role/grant assertions and cross-owner UPDATE/DELETE/updateMany/deleteMany coverage;
• make future multi-table RLS migrations explicitly transactional;
• do not grant broad User, AuditLog, or UserSuspension table access to the app runtime; use narrow views/functions or service identities;
• getPrisma(locale) currently caches an extended client outside the active RLS transaction, so it must be made context-aware before protecting localized/nested paths;
• Upload/HomeworkCompletion remain mixed-visibility families and require narrow public entitlement/aggregate interfaces before owner policies.