---
name: rtie-schema-and-migrations
description: Author Drizzle schema changes and run migrations for RTIE — correct tooling, table rules, index patterns, and the context_id contract.
version: "0.1.0"
---

# RTIE Schema and Migrations

RTIE uses **Drizzle ORM** with **Postgres on Supabase**, accessed via **HyperDrive** from CF Workers.

## Never use these for migrations

- ❌ Supabase MCP (mcp.supabase.com) — only manages Supabase, not RTIE module tables
- ❌ Supabase CLI (`supabase db push`) — bypasses RTIE's migration runner
- ❌ Drizzle Studio — read-only UI, not the apply path

Use: `bun db:migrate` (applies files in `drizzle/` in lexical order, records each SHA in `__drizzle_migrations`).

## Key commands

```bash
# Apply pending migrations
source ~/.zshrc && bun db:migrate

# Generate a new migration from schema changes
bun db:generate

# Introspect current DB state (to check schema drift)
source ~/.zshrc && bun db:introspect
```

## Schema location

```
packages/db/src/schema/
  auth.ts        # users, tenant_members, tenant_invites
  tenants.ts     # tenants, api_keys, module_enablements, plans
  rooms.ts       # rooms, room_members
  chat.ts        # messages
  auctions.ts    # auctions, bids, listings
  actions.ts     # signals, rules, action_log
  social.ts      # follows, reactions, posts
  ...
drizzle/         # migration SQL files (00001_init.sql, 00002_add_rooms.sql ...)
```

## Table authoring rules

### 1. Every table gets context_id (modules only)

Any table that belongs to a business workflow must be anchored to the Context Graph:

```typescript
import { pgTable, uuid, text, timestamp } from "drizzle-orm/pg-core";
import { contexts } from "./contexts.js";

export const myThings = pgTable("my_things", {
  id:         uuid("id").primaryKey().defaultRandom(),
  tenantId:   uuid("tenant_id").notNull(),
  contextId:  uuid("context_id").notNull().references(() => contexts.id), // required
  name:       text("name").notNull(),
  createdAt:  timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt:  timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});
```

**Exempt tables** (document the exemption in a migration comment):
- Global lookup/catalog tables (e.g. module catalog, provider config)
- Tenant-level tables that exist before any context (e.g. `tenants`, `api_keys`)

### 2. Use timestamptz — not timestamp without time zone

```typescript
// Correct
timestamp("created_at", { withTimezone: true })

// Wrong — ambiguous timezone
timestamp("created_at")
```

### 3. Primary keys: UUID not bigint or serial

```typescript
id: uuid("id").primaryKey().defaultRandom()  // ✅
id: serial("id").primaryKey()                // ❌ — not portable, leaks count
```

### 4. Soft deletes use deletedAt, not status flags

```typescript
deletedAt: timestamp("deleted_at", { withTimezone: true }),  // null = active
```

### 5. Foreign keys need indexes

```typescript
export const myThingsIdx = index("my_things_tenant_id_idx").on(myThings.tenantId);
export const myThingsCtxIdx = index("my_things_context_id_idx").on(myThings.contextId);
```

### 6. GIN indexes for JSONB columns

```typescript
export const signalsPayloadIdx = index("signals_payload_idx")
  .on(signals.payload)
  .using("gin");
```

## Querying (Workers / HyperDrive)

```typescript
import { drizzle } from "drizzle-orm/postgres-js";

// In a Hono handler — use getRequestDb from middleware (handles HyperDrive connection)
import { getRequestDb, getTenantId } from "@rtie/middleware/hono";

myModuleRouter.get("/v1/my-module/things", async (c) => {
  const db = await getRequestDb(c);   // ← HyperDrive-pooled
  const tenantId = getTenantId(c);    // ← from JWT/API key context
  const things = await db.query.myThings.findMany({
    where: (t, { eq }) => eq(t.tenantId, tenantId),
    orderBy: (t, { desc }) => desc(t.createdAt),
    limit: 50,
  });
  return c.json(things);
});
```

## Cursor pagination (large tables)

Never use `OFFSET` for paginated results on large tables:

```typescript
// Correct — cursor-based
const items = await db.query.myThings.findMany({
  where: (t, { and, eq, lt }) =>
    and(eq(t.tenantId, tenantId), cursor ? lt(t.createdAt, cursor) : undefined),
  orderBy: (t, { desc }) => desc(t.createdAt),
  limit: 20,
});
```

## Row-Level Security

Tables used by Supabase Auth users (B2C buyers, console users) should have RLS enabled.
Workers (server-side) bypass RLS via the service key.

```sql
-- Enable RLS on a table
ALTER TABLE my_things ENABLE ROW LEVEL SECURITY;

-- Workers bypass via service key — no extra policy needed for server paths
```

Full schema docs: https://docs.rtie.ai/build/schema