Drizzle setup + drizzle-kit pull schema.ts from basketball db

forgejo_admin commented

2026-04-25 21:58:27 +00:00

Owner

Type

Feature

Lineage

Hard depends on forgejo_admin/westside-admin#6 (scaffolding — repo must have package.json, src/lib/server/ directory). Hard depends on forgejo_admin/pal-e-platform#302 (admin_app Postgres user — needed for the integration AC and CI drift check).

Repo

forgejo_admin/westside-admin

User Story

story-westside-admin-admin-row-crud. Implements the schema introspection safety constraint — schema is generated from live DB, not hand-typed.

Context

Drizzle ORM provides type-safe SQL via a TypeScript schema file. Rather than hand-write the schema for 22 tables × ~10 columns, we use drizzle-kit pull to introspect the live basketball Postgres and generate src/lib/server/schema.ts. This file is the source of truth for what tables/columns exist and is checked into git.

The basketball DB has 22 tables and 18 enums (verified). Sensitive columns (oauth_tokens.token_data, password_reset_tokens.token, parents.registration_token, players.contract_token) need to remain in the schema (so updates work) but get hidden via a separate visible-columns config in a later ticket. See arch-domain-westside-admin for the full table inventory.

DB credentials come from DATABASE_URL env var. v1 connects as the admin_app Postgres user (created in forgejo_admin/pal-e-platform#302).

File Targets

Create:

drizzle.config.ts — drizzle-kit config: dialect=postgresql, schema=src/lib/server/schema.ts, dbCredentials from env
src/lib/server/db.ts — exports db from drizzle(pg.Pool) using DATABASE_URL. Marked @internal; consumed only by query helper (#3)
src/lib/server/schema.ts — generated by drizzle-kit pull. CHECKED INTO GIT.
package.json — add drizzle-orm, drizzle-kit, pg, @types/pg, npm script db:pull
scripts/check-schema-drift.sh — local-runnable script that runs db:pull against DATABASE_URL and compares to committed schema.ts, exits non-zero on diff

Do NOT create:

Tenant-scoping helper (covered by #3)
Woodpecker CI step that runs the drift check — that wiring belongs in forgejo_admin/westside-admin#8 (.woodpecker.yaml). This ticket only provides the script.

Acceptance Criteria

npm run db:pull connects to basketball db and regenerates schema.ts
All 22 tables introspect (alembic_version included; will be excluded at UI layer)
All 18 enums introspect as pgEnum() declarations
schema.ts is committed to git
scripts/check-schema-drift.sh runs locally with DATABASE_URL set and exits 0 if schema matches, non-zero on diff
db.ts lazy-initializes pool on first import; closes cleanly on SIGTERM

Test Expectations

Unit test: import schema.ts and assert that players has at least 30 columns (sanity check against introspection)
Integration test: db.select().from(schema.tenants).limit(1) returns at least 1 row when DATABASE_URL is set to the admin_app credentials

Constraints

Use pg driver (not postgres-js) to match the connection pool pattern westside-app uses
jsonb columns introspect as unknown — manually narrow oauth_tokens.token_data, players.contract_overrides, teams.contract_config, contract_audit_log.old_state/new_state in a typed extension file (or document and defer)

Checklist

PR opened
npm run db:pull reproducible
Drift check script verified locally (corrupt schema.ts, confirm exit non-zero)

project-westside-admin
arch-domain-westside-admin
arch-dataflow-westside-admin
DEPENDS ON: #6 (scaffolding), forgejo_admin/pal-e-platform#302 (admin_app Postgres user)

### Type Feature ### Lineage Hard depends on `forgejo_admin/westside-admin#6` (scaffolding — repo must have `package.json`, `src/lib/server/` directory). Hard depends on `forgejo_admin/pal-e-platform#302` (admin_app Postgres user — needed for the integration AC and CI drift check). ### Repo `forgejo_admin/westside-admin` ### User Story `story-westside-admin-admin-row-crud`. Implements the **schema introspection** safety constraint — schema is generated from live DB, not hand-typed. ### Context Drizzle ORM provides type-safe SQL via a TypeScript schema file. Rather than hand-write the schema for 22 tables × ~10 columns, we use `drizzle-kit pull` to introspect the live basketball Postgres and generate `src/lib/server/schema.ts`. This file is the source of truth for what tables/columns exist and is checked into git. The basketball DB has 22 tables and 18 enums (verified). Sensitive columns (oauth_tokens.token_data, password_reset_tokens.token, parents.registration_token, players.contract_token) need to remain in the schema (so updates work) but get hidden via a separate visible-columns config in a later ticket. See `arch-domain-westside-admin` for the full table inventory. DB credentials come from `DATABASE_URL` env var. v1 connects as the `admin_app` Postgres user (created in `forgejo_admin/pal-e-platform#302`). ### File Targets Create: - `drizzle.config.ts` — `drizzle-kit` config: dialect=postgresql, schema=src/lib/server/schema.ts, dbCredentials from env - `src/lib/server/db.ts` — exports `db` from `drizzle(pg.Pool)` using `DATABASE_URL`. Marked `@internal`; consumed only by query helper (#3) - `src/lib/server/schema.ts` — generated by `drizzle-kit pull`. CHECKED INTO GIT. - `package.json` — add `drizzle-orm`, `drizzle-kit`, `pg`, `@types/pg`, npm script `db:pull` - `scripts/check-schema-drift.sh` — local-runnable script that runs `db:pull` against `DATABASE_URL` and compares to committed `schema.ts`, exits non-zero on diff Do NOT create: - Tenant-scoping helper (covered by #3) - Woodpecker CI step that runs the drift check — that wiring belongs in `forgejo_admin/westside-admin#8` (.woodpecker.yaml). This ticket only provides the script. ### Acceptance Criteria - [ ] `npm run db:pull` connects to basketball db and regenerates `schema.ts` - [ ] All 22 tables introspect (alembic_version included; will be excluded at UI layer) - [ ] All 18 enums introspect as `pgEnum()` declarations - [ ] `schema.ts` is committed to git - [ ] `scripts/check-schema-drift.sh` runs locally with `DATABASE_URL` set and exits 0 if schema matches, non-zero on diff - [ ] `db.ts` lazy-initializes pool on first import; closes cleanly on SIGTERM ### Test Expectations - Unit test: import schema.ts and assert that `players` has at least 30 columns (sanity check against introspection) - Integration test: `db.select().from(schema.tenants).limit(1)` returns at least 1 row when DATABASE_URL is set to the admin_app credentials ### Constraints - Use `pg` driver (not `postgres-js`) to match the connection pool pattern westside-app uses - jsonb columns introspect as `unknown` — manually narrow `oauth_tokens.token_data`, `players.contract_overrides`, `teams.contract_config`, `contract_audit_log.old_state/new_state` in a typed extension file (or document and defer) ### Checklist - [ ] PR opened - [ ] `npm run db:pull` reproducible - [ ] Drift check script verified locally (corrupt schema.ts, confirm exit non-zero) ### Related - `project-westside-admin` - `arch-domain-westside-admin` - `arch-dataflow-westside-admin` - DEPENDS ON: `#6` (scaffolding), `forgejo_admin/pal-e-platform#302` (admin_app Postgres user)

forgejo_admin commented

2026-04-25 22:07:27 +00:00

Author

Owner

Scope Review: NEEDS_REFINEMENT

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

Scope is solid for a greenfield Drizzle setup; three fixable issues before backlog -> todo:

[BODY] "Depends on scaffolding ticket" is vague — replace with "Blocked by westside-admin#6 (scaffolding) and pal-e-platform#302 (admin_app DB user)".
[BODY] CI drift AC depends on Woodpecker wiring (westside-admin#8, also backlog). Either narrow AC to "script exists, runnable locally" or add explicit #8 dependency.
[SCOPE] arch:scopeddb-helper label has no backing arch note. Project page lists arch-domain/dataflow/deployment only. Either create arch-scopeddb-helper or re-label (arch-domain-westside-admin is closest). Same label is on issue #3, decide jointly.

No decomposition needed (4 files, 6 ACs, ~5–10 min agent work).

## Scope Review: NEEDS_REFINEMENT Review note: `review-1089-2026-04-25` Scope is solid for a greenfield Drizzle setup; three fixable issues before backlog -> todo: - `[BODY]` "Depends on scaffolding ticket" is vague — replace with "Blocked by westside-admin#6 (scaffolding) and pal-e-platform#302 (admin_app DB user)". - `[BODY]` CI drift AC depends on Woodpecker wiring (westside-admin#8, also backlog). Either narrow AC to "script exists, runnable locally" or add explicit #8 dependency. - `[SCOPE]` `arch:scopeddb-helper` label has no backing arch note. Project page lists arch-domain/dataflow/deployment only. Either create `arch-scopeddb-helper` or re-label (arch-domain-westside-admin is closest). Same label is on issue #3, decide jointly. No decomposition needed (4 files, 6 ACs, ~5–10 min agent work).

Rows
Columns

Drizzle setup + drizzle-kit pull schema.ts from basketball db #1