database

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database Skill

## 1. When to Use
Use this skill when defining database tables, Drizzle ORM relations, generating migrations, writing seed routines, or executing database queries.

## 2. Schema Conventions
- Define tables in modular files under `packages/db/src/schema/<module>.ts`.
- Re-export all tables in `packages/db/src/schema/index.ts`.
- Use UUID primary keys: `id: uuid('id').defaultRandom().primaryKey()`.
- Always include timestamps:
  ```typescript
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  ```
- Always define foreign keys with appropriate `onDelete` cascade or restrict behaviors.
- Index lookup columns (e.g. `email`, `status`, `userId`, `tokenHash`).

## 3. Migrations & Seed Idempotency
- Generate migrations: `pnpm db:generate`.
- Apply migrations: `pnpm db:migrate`.
- Seed data idempotently: `pnpm db:seed`.
- The seed runner must check for existing records (e.g. `ROOT` account, default policies) and never overwrite or reset user data on repeated runs.
- Tenancy: `users.tenant_id` is the owner; `roles`, `groups`, `policies` carry a nullable `tenant_id` where NULL means platform-owned. Name uniqueness is per owner via a partial index (NULL) plus `(tenant_id, name)`. The seed creates a default workspace and adopts any pre-tenancy users into it.
- `drizzle-kit generate` emits the full schema when the previous migration has no snapshot. Write incremental migration SQL by hand and keep the generated snapshot so later diffs are correct.
- Tests use `createTestDatabase()` from `@packages/db/test-support`: a real in-process Postgres with every migration applied. Add an upgrade test (legacy rows survive) for every migration.

## 4. Mandatory Testing Expectations
Every database schema change requires:
1. **Migration Verification**: Validating that Drizzle migration files generate cleanly and apply without syntax errors.
2. **Constraint Testing**: Verifying that unique indexes (e.g. unique emails) and foreign keys enforce data integrity.
3. **Seed Idempotency**: Testing that executing the seed runner repeatedly produces zero duplicate rows and preserves existing credentials.

From Mihir-Rabari/create-odoo-app, MIT.

More from Mihir-Rabari/create-odoo-app