Set Up @packages/db

Overview

Build the @packages/db workspace package — the Drizzle ORM schema for the LibSQL operational layer. Defines all 38 normalized tables from model.md as TypeScript schema files, generates migrations via Drizzle Kit, and exports typed query builders for use by @packages/sync (seed.ts refactor) and @services/dashboard.

Result: bunx drizzle-kit generate produces valid migrations; bunx drizzle-kit push applies them to atlas-ops.db without errors.

Goals

• Scaffold @packages/db as a Bun workspace package
• Implement all 7 schema files covering all 38 tables from model.md
• Configure Drizzle Kit (drizzle.config.ts) and LibSQL client (client.ts)
• Generate migrations and apply to atlas-ops.db
• Export all schema tables and the Drizzle instance from the package index
• Verify: type-check passes, migrations generate and push cleanly

Non-Goals

• Populating operational tables with order/person data (that belongs to a future sync enhancement or manual import)
• Dashboard queries (Plan 007 consumes @packages/db)
• Turso remote deployment
• Any transformation or business logic — schema only
• Backfilling historical data from xlsx into LibSQL (out of scope for this plan)

Phases

• Phase 1: Package scaffold (package.json, tsconfig, eslint, drizzle.config.ts, client.ts)
• Phase 2: Core + database schema files (core.ts, db.ts)
• Phase 3: Catalog + commerce + finance schema files (catalog.ts, commerce.ts, finance.ts)
• Phase 4: Marketing + target schema files (marketing.ts, target.ts)
• Phase 5: Migration generation + push verification

Success

• @packages/db scaffolded with all config files
• All 7 schema files implemented covering all 38 tables from model.md
• bun run test:type --filter @packages/db passes
• bunx drizzle-kit generate produces migration files in migrations/
• bunx drizzle-kit push applies schema to atlas-ops.db without errors
• All table names, column names, and FK relationships match model.md exactly

Requirements

• drizzle-orm and drizzle-kit available in workspace catalogs
• @libsql/client available in workspace catalogs
• @repo/typescript/lib tsconfig preset
• @repo/lint/base ESLint config
• ulid package for ULID generation (or use crypto.randomUUID as fallback)
• model.md as the authoritative schema reference

Context

Why This Approach

• Drizzle ORM with LibSQL dialect gives type-safe queries without a separate server
• Schema-as-code: all 38 tables are version-controlled TypeScript
• Split into 7 domain files (core, db, catalog, commerce, finance, marketing, target) matching the model.md groupings — easier to navigate and maintain
• ULIDs for PKs: sortable, URL-safe, no coordination needed for generation
• Migrations generated by Drizzle Kit: reproducible schema evolution

Key Constraints

• All PKs are text (ULID format) — not integer autoincrement
• FK columns reference parent table PKs as text — Drizzle references(() => table.id) syntax
• entity_id present on all tables that require entity scoping (per model.md)
• Join tables (M2M) use composite PKs (two FK columns), no separate id column
• decimal columns use real in SQLite/LibSQL (no native DECIMAL type)
• bool columns use integer (0/1) in LibSQL via Drizzle's .default(false) pattern
• date columns use text in ISO format (YYYY-MM-DD) per LibSQL convention

Edge Cases

• catalog_price: either item_id or variant_id must be set (not both null) — constraint enforced at application layer, not DB level (LibSQL doesn't support CHECK constraints with subqueries)
• commerce_order.participant_id is nullable (required only for Enrollment type) — application-level enforcement
• Self-referential FKs in db_relation (from_person_id → db_person, to_person_id → db_person) — Drizzle handles this with deferred FK resolution
• marketing_agenda unit_id nullable (entity-level agendas have no unit)

Tradeoffs

• real for decimals: minor precision loss vs application-layer rounding for IDR amounts; acceptable given the reporting use case (amounts displayed rounded to nearest Rp)
• No CHECK constraints: LibSQL support is limited; constraints enforced in application layer (sync + dashboard)

Skills

• plan — plan file format and conventions

Boundaries

• Always: Update Progress.md after each task completion
• Always: Table names, column names, and FK relationships must exactly match model.md
• Always: Use ULID text PKs (not integer)
• Always: Use workspace:* for @repo/lint and @repo/typescript
• Ask first: Any schema change that deviates from model.md
• Ask first: Adding tables not in model.md
• Never: Add business logic or queries to schema files (schema only)
• Never: Commit atlas-ops.db to git

Questions

• PK type? → ULID as text
• Decimal representation? → real (LibSQL limitation)
• Bool representation? → integer via Drizzle default
• Should client.ts read DATABASE_URL from env, or hardcode file:atlas-ops.db as default?
• Should the package export a seed() helper stub to replace the raw SQL in sync's seed.ts?