Skip to content

feat(db): make user section subscriptions explicit before RLS activation #604

Description

@tiankaima

Context

The RLS inventory missed Prisma's implicit _UserCalendarSections join behind User.subscribedSections. It is pure owner data, but current subscription reads/writes use nested connect/disconnect and localized Prisma clients outside an explicit user RLS transaction. Applying a raw policy to implicit A/B columns would be brittle and getPrisma(locale) is not currently transaction-context aware.

Blocked by the production runtime identity/cache correction in #603. Part of #601.

Proposed sequence

  1. Add explicit UserSectionSubscription(userId, sectionId, createdAt) with stable unique/indexes.
  2. Backfill from _UserCalendarSections in an explicit transactional migration without dropping the old relation.
  3. Route subscription read/write/page services through an explicit TransactionClient; do not rely on cached getPrisma(locale) escaping ALS.
  4. Add REST, GraphQL, MCP, dashboard/calendar parity tests under the non-owner runtime role.
  5. Cut reads/writes to the explicit model, verify counts/IDs, then remove the implicit join in a later migration.
  6. In a separate activation migration, ENABLE + FORCE RLS with owner USING/WITH CHECK and exact runtime grants.

Acceptance criteria

  • Migration is transactional, restart-safe, and proves row-count/key parity.
  • Owner columns have supporting unique/index definitions.
  • Missing context default-denies reads and every write form.
  • Cross-user SELECT/INSERT/UPDATE/DELETE/upsert/updateMany/deleteMany are isolated or rejected.
  • Full subscription REST/GraphQL/MCP service flows pass as LOGIN NOINHERIT NOBYPASSRLS.
  • Account deletion cascade remains functional without broad User table grants.
  • Hyperdrive runtime binding is cache-disabled and verified through security(db): replace production Hyperdrive postgres origin with cache-disabled RLS runtime role #603 before activation.

Metadata

Metadata

Assignees

No one assigned

    Labels

    area: dataPrisma, migrations, seed/import pipelines, transactions, and persistence invariantsenhancementNew feature or request

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions