forgejo_admin/westside-admin
1
0
Fork 0
Code Issues 6 Pull requests Projects Releases Packages Wiki Activity Actions

Tenant-scoped Drizzle query helper #3

New issue
Open
opened 2026-04-25 21:59:08 +00:00 by forgejo_admin · 3 comments
forgejo_admin commented 2026-04-25 21:59:08 +00:00
Owner
Copy link

Type

Feature

Lineage

Hard depends on forgejo_admin/westside-admin#1 (Drizzle setup — needs db.ts to wrap and schema.ts to type-check against). Hard depends on forgejo_admin/westside-admin#6 (scaffolding).

Repo

forgejo_admin/westside-admin

User Story

story-westside-admin-admin-row-crud. Implements the tenant scoping safety constraint.

Context

Nearly every basketball-api table has a tenant_id column. Forgetting WHERE tenant_id = ? in even one query is a data-leak bug. The fix is to make tenant scoping a property of the query helper, not a discipline applied per call site.

For v1, Westside is the only tenant. tenant_id is sourced from a single constant (TENANT_ID = 1), NOT from the JWT. The constant lives in one well-marked file as the swap point for future multi-tenant.

The unscoped db from src/lib/server/db.ts is NOT exported from $lib/server. Only the scoped helper is. A lint or grep-based CI check enforces this.

File Targets

Create:

  • src/lib/server/tenant.ts — exports TENANT_ID = 1 with a top-of-file comment marking the future multi-tenant swap point
  • src/lib/server/scopedDb.ts — exports scopedDb wrapper. Provides select(table), update(table), insert(table) builders that auto-inject WHERE tenant_id = TENANT_ID for tenant-scoped tables. Tables without tenant_id (alembic_version, player_teams) accessed via separate systemDb builder.

Modify:

  • src/lib/server/db.ts — keep unscoped db but mark with @internal JSDoc; only scopedDb.ts should import it
  • package.json — add lint script that fails on any import { db } from '$lib/server/db' outside scopedDb.ts

Do NOT modify:

  • Existing src/routes/ (no consumers yet)

Acceptance Criteria

  • scopedDb.select(players) generates SQL with WHERE tenant_id = 1 automatically
  • scopedDb.update(players).set({...}).where(eq(players.id, 5)) generates WHERE id = 5 AND tenant_id = 1
  • Type system distinguishes tenant tables (have tenant_id column) from system tables — using a system table via scopedDb is a TypeScript error
  • CI lint check fails if a file outside scopedDb.ts imports unscoped db
  • Unit tests cover: select scoping, update scoping with extra where clauses, insert with auto-injected tenant_id, type error on system table misuse
  • tenant.ts has a multi-line block comment explaining the swap procedure when a second tenant exists

Test Expectations

  • Unit: 6+ tests covering scoped select/update/insert + type error cases (use expectTypeOf or similar)
  • Integration: actual query against basketball db (tenant_id=1) returns rows; manually mutating tenant_id in URL or form body still produces correct WHERE clause

Constraints

  • DO NOT pull tenant_id from JWT yet. The whole point of v1 is to defer that until needed.
  • Helper must use Drizzle's typed builders, not raw SQL strings — scoping must compose with arbitrary where/and/or

Checklist

  • PR opened
  • All ACs covered by tests
  • Lint check verified (try removing scoping and confirm CI fails)

Related

  • project-westside-admin
  • arch-dataflow-westside-admin
  • DEPENDS ON: #6 (scaffolding), #1 (Drizzle setup)
### Type Feature ### Lineage Hard depends on `forgejo_admin/westside-admin#1` (Drizzle setup — needs `db.ts` to wrap and `schema.ts` to type-check against). Hard depends on `forgejo_admin/westside-admin#6` (scaffolding). ### Repo `forgejo_admin/westside-admin` ### User Story `story-westside-admin-admin-row-crud`. Implements the **tenant scoping** safety constraint. ### Context Nearly every basketball-api table has a `tenant_id` column. Forgetting `WHERE tenant_id = ?` in even one query is a data-leak bug. The fix is to make tenant scoping a property of the query helper, not a discipline applied per call site. For v1, Westside is the only tenant. tenant_id is sourced from a single constant (`TENANT_ID = 1`), NOT from the JWT. The constant lives in one well-marked file as the swap point for future multi-tenant. The unscoped `db` from `src/lib/server/db.ts` is NOT exported from `$lib/server`. Only the scoped helper is. A lint or grep-based CI check enforces this. ### File Targets Create: - `src/lib/server/tenant.ts` — exports `TENANT_ID = 1` with a top-of-file comment marking the future multi-tenant swap point - `src/lib/server/scopedDb.ts` — exports `scopedDb` wrapper. Provides `select(table)`, `update(table)`, `insert(table)` builders that auto-inject `WHERE tenant_id = TENANT_ID` for tenant-scoped tables. Tables without tenant_id (alembic_version, player_teams) accessed via separate `systemDb` builder. Modify: - `src/lib/server/db.ts` — keep unscoped `db` but mark with `@internal` JSDoc; only `scopedDb.ts` should import it - `package.json` — add lint script that fails on any `import { db } from '$lib/server/db'` outside `scopedDb.ts` Do NOT modify: - Existing `src/routes/` (no consumers yet) ### Acceptance Criteria - [ ] `scopedDb.select(players)` generates SQL with `WHERE tenant_id = 1` automatically - [ ] `scopedDb.update(players).set({...}).where(eq(players.id, 5))` generates `WHERE id = 5 AND tenant_id = 1` - [ ] Type system distinguishes tenant tables (have `tenant_id` column) from system tables — using a system table via scopedDb is a TypeScript error - [ ] CI lint check fails if a file outside `scopedDb.ts` imports unscoped `db` - [ ] Unit tests cover: select scoping, update scoping with extra where clauses, insert with auto-injected tenant_id, type error on system table misuse - [ ] `tenant.ts` has a multi-line block comment explaining the swap procedure when a second tenant exists ### Test Expectations - Unit: 6+ tests covering scoped select/update/insert + type error cases (use `expectTypeOf` or similar) - Integration: actual query against basketball db (tenant_id=1) returns rows; manually mutating tenant_id in URL or form body still produces correct WHERE clause ### Constraints - DO NOT pull tenant_id from JWT yet. The whole point of v1 is to defer that until needed. - Helper must use Drizzle's typed builders, not raw SQL strings — scoping must compose with arbitrary `where`/`and`/`or` ### Checklist - [ ] PR opened - [ ] All ACs covered by tests - [ ] Lint check verified (try removing scoping and confirm CI fails) ### Related - `project-westside-admin` - `arch-dataflow-westside-admin` - DEPENDS ON: `#6` (scaffolding), `#1` (Drizzle setup)
forgejo_admin commented 2026-04-25 22:07:55 +00:00
Author
Owner
Copy link

Scope Review: NEEDS_REFINEMENT

Review note: review-1091-2026-04-25

Strong ticket — scope, ACs, file targets, and constraints are crisp. Two minor items before todo:

  • [SCOPE] arch:scopeddb-helper label has no backing arch note. Project page lists arch-domain-westside-admin, arch-dataflow-westside-admin, arch-deployment-westside-admin. The issue's Related section already points to arch-dataflow-westside-admin. Recommend relabeling board item #1091 to arch:dataflow-westside-admin (Ava's call).
  • [BODY] Pin Lineage to explicit issue numbers: "Depends on #1 (Drizzle setup) which depends on #6 (scaffolding)."

No decomposition needed. File targets cannot be live-verified (repo is empty pre-#6/#1) but paths are well-formed and consistent with the dependency chain.

## Scope Review: NEEDS_REFINEMENT Review note: `review-1091-2026-04-25` Strong ticket — scope, ACs, file targets, and constraints are crisp. Two minor items before todo: - `[SCOPE]` `arch:scopeddb-helper` label has no backing arch note. Project page lists `arch-domain-westside-admin`, `arch-dataflow-westside-admin`, `arch-deployment-westside-admin`. The issue's Related section already points to `arch-dataflow-westside-admin`. Recommend relabeling board item #1091 to `arch:dataflow-westside-admin` (Ava's call). - `[BODY]` Pin Lineage to explicit issue numbers: "Depends on #1 (Drizzle setup) which depends on #6 (scaffolding)." No decomposition needed. File targets cannot be live-verified (repo is empty pre-#6/#1) but paths are well-formed and consistent with the dependency chain.
forgejo_admin commented 2026-04-25 23:42:23 +00:00
Author
Owner
Copy link

Scope Re-Review: NEEDS_REFINEMENT

Review note: review-1091-2026-04-25-v2 (re-review of review-1091-2026-04-25)

Resolved since v1:

  • [BODY] Lineage now pins explicit issue numbers (#1, #6) — clean.
  • [SCOPE] Arch label changed to arch:scoped-db, matching sibling #1089 (Drizzle setup). Helper layer is consistent.

Still outstanding:

  • [SCOPE] No backing arch-scoped-db note exists in pal-e-docs. The arch label points nowhere; the issue's Related section points at arch-dataflow-westside-admin. Recommend creating arch-scoped-db-westside-admin (or similar) as a sub-component note documenting the helper API surface, tenant-vs-system table split, and lint-enforced single entry point — backs both #1089 and this ticket.

Ticket scope, ACs, file targets, and constraints are otherwise READY. Flips to APPROVED once the arch note lands or Ava waives the requirement.

## Scope Re-Review: NEEDS_REFINEMENT Review note: `review-1091-2026-04-25-v2` (re-review of `review-1091-2026-04-25`) **Resolved since v1:** - `[BODY]` Lineage now pins explicit issue numbers (#1, #6) — clean. - `[SCOPE]` Arch label changed to `arch:scoped-db`, matching sibling #1089 (Drizzle setup). Helper layer is consistent. **Still outstanding:** - `[SCOPE]` No backing `arch-scoped-db` note exists in pal-e-docs. The arch label points nowhere; the issue's Related section points at `arch-dataflow-westside-admin`. Recommend creating `arch-scoped-db-westside-admin` (or similar) as a sub-component note documenting the helper API surface, tenant-vs-system table split, and lint-enforced single entry point — backs both #1089 and this ticket. Ticket scope, ACs, file targets, and constraints are otherwise READY. Flips to APPROVED once the arch note lands or Ava waives the requirement.
forgejo_admin commented 2026-04-25 23:45:34 +00:00
Author
Owner
Copy link

Scope Review: APPROVED

Review note: review-1091-2026-04-25-v3

v2 NEEDS_REFINEMENT resolved. New arch note arch-scoped-db-westside-admin exists and maps the arch:scoped-db label to real components (scopedDb + systemDb), nested under arch-dataflow-westside-admin. Traceability triangle is complete — story note, arch note, and Forgejo issue all verified.

Ready to advance backlog → todo.

Optional (non-gating): Related section could also list arch-scoped-db-westside-admin alongside arch-dataflow-westside-admin. Not required — the parent-arch link is sufficient.

## Scope Review: APPROVED Review note: `review-1091-2026-04-25-v3` v2 NEEDS_REFINEMENT resolved. New arch note `arch-scoped-db-westside-admin` exists and maps the `arch:scoped-db` label to real components (`scopedDb` + `systemDb`), nested under `arch-dataflow-westside-admin`. Traceability triangle is complete — story note, arch note, and Forgejo issue all verified. Ready to advance backlog → todo. Optional (non-gating): Related section could also list `arch-scoped-db-westside-admin` alongside `arch-dataflow-westside-admin`. Not required — the parent-arch link is sufficient.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
No results found.
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
forgejo_admin/westside-admin#3
No description provided.