# DB Workflow

> Database workflow with Postgres, Kysely, and Atlas migrations. Use when modifying database schema, creating migrations, generating TypeScript types, or troubleshooting database issues. Triggers on "schema change", "migration", "db-migrate", "kysely", "atlas", or when editing db/schema.sql. Use when this capability is needed.

- Skill: `tomevault-io/db-workflow` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/db-workflow`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/db-workflow/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/db-workflow

---


# Database Workflow

**Source of truth:** `db/schema.sql` is the canonical schema.

## Quick Commands

| Command | Purpose |
|---------|---------|
| `just db-migrate` | Apply schema directly (fast iteration) |
| `just db-migration-new <name>` | Create versioned migration |
| `just db-migration-apply` | Apply versioned migrations |
| `just db-migration-status` | Check migration status |
| `just db-schema` | Generate TypeScript types |

## Workflow Decision Tree

```
Schema change needed?
├─ Early development / experimenting
│  └─ Edit db/schema.sql → just db-migrate → just db-schema
│
└─ Production / reviewable history needed
   └─ Edit db/schema.sql → just db-migration-new <name> → just db-migration-apply → just db-schema
```

## Local Dev Setup

```bash
# 1. Start Postgres
docker compose up -d  # or: just setup

# 2. Apply schema
just db-migrate

# 3. Generate TypeScript types (after schema changes)
just db-schema
```

## Schema Changes Checklist

1. Edit `db/schema.sql`
2. Apply locally: `just db-migrate`
3. Generate types: `just db-schema`
4. Update any affected services/routers
5. Run tests: `pnpm test`

## Versioned Migrations (Atlas)

Even with versioned migrations, `db/schema.sql` stays the source of truth. Atlas uses it to infer changes.

```bash
# Create migration from schema diff
just db-migration-new add_user_roles

# Apply migrations
just db-migration-apply

# Check status
just db-migration-status
```

## Conventions

- Use `uuid` primary keys
- Use `created_at` / `updated_at` timestamps
- Enforce tenancy at service layer (e.g., `todos.user_id`)
- Columns mapped to camelCase in TypeScript via Kysely plugin

## Runtime DB Access

```typescript
// packages/backend/core/src/db.ts
import { connectDB, getDB } from "@yourcompany/backend-core/db";

// At app startup (once)
await connectDB({ connectionString: config.DATABASE_URL });

// Anywhere after initialization
const db = getDB();
const users = await db.selectFrom("user").selectAll().execute();
```

## Why Kysely (vs Drizzle/Prisma)

- **Typed query builder** without ORM "model" layer
- Works with **SQL-first** workflow (`db/schema.sql`) + codegen
- Easy to pass `db` dependency for testing

## Gotchas

- Tests apply `db/schema.sql` into testcontainers Postgres
- After schema changes:
  - Update `db/schema.sql`
  - Re-apply locally (`just db-migrate`)
  - Re-generate TS types (`just db-schema`)
- Generated types: `packages/backend/core/src/schema.ts`

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/nikola-milovic) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-11 -->