typed data layer

SKILL.md

---
description: build a typed postgres data layer with drizzle. schema, queries, migrations, RLS pattern. trigger on "set up the database", "data layer", "orm", "typed queries".
---

# typed data layer

the SaaS spine: a typed schema, safe queries, and migrations, without losing tenant isolation. uses the verified drizzle-orm module.

## intent

take a builder from "i need a database" to a typed schema with migrations and a tenant-isolation pattern that actually holds. the failure mode this prevents: an orm-only setup that filters by tenant in app code, which one forgotten where-clause turns into a cross-tenant data leak.

## inputs

- a postgres database + connection string
- DATABASE_URL in env
- drizzle-kit for migrations

## procedure

### step 1, define the schema with types

```js
const { pgTable, uuid, text, timestamp } = require('drizzle-orm/pg-core');
const orgs = pgTable('orgs', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
});
```

### step 2, query with inferred types

```js
const { drizzle } = require('drizzle-orm/node-postgres');
const db = drizzle(pool);
const rows = await db.select().from(orgs);
```

### step 3, generate + apply migrations

use drizzle-kit to diff the schema and emit SQL migrations, never hand-edit the db.

### step 4, enforce isolation in the database, not just the app

for multi-tenant apps add postgres RLS policies so the database refuses cross-tenant reads even if app code forgets a filter.

## decision points

- migrations: generated + reviewed, committed to the repo, never ad-hoc.
- RLS: mandatory for multi-tenant, the orm filter is a convenience not a guarantee.
- connection pooling: use a pooler for serverless or you exhaust connections.

## output contract

a typed schema, generated migrations in the repo, type-inferred queries, and (for multi-tenant) postgres RLS policies that enforce isolation at the database.

## outcome signal

success means a deliberately filter-less tenant query returns zero foreign rows because RLS blocks it. if isolation depends only on remembering a where-clause, the layer is unsafe.