> ## Documentation Index
> Fetch the complete documentation index at: https://docs.latwoodtech.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

# Database Runbook

This runbook covers the migration workflow, Neon branch strategy, and RLS testing for Factory apps.

## Architecture

Every Factory app gets an isolated **Neon project** (not just a schema). This ensures:

* No cross-app data leakage at the database level
* Independent scaling and compute pause per app
* Clean isolation during app transfer to a buyer

Connections from the Cloudflare Worker are routed through a **Hyperdrive** binding (`env.DB`) for connection pooling and latency reduction.

## Neon branch strategy

| Branch            | Purpose                                    |
| ----------------- | ------------------------------------------ |
| `main`            | Production database                        |
| `staging`         | Staging environment (branched from `main`) |
| Per-PR (optional) | Ephemeral branches for integration testing |

```bash theme={null}
# Create a staging branch from main
neonctl branches create --project-id <PROJECT_ID> --name staging --parent main

# List branches
neonctl branches list --project-id <PROJECT_ID>

# Delete ephemeral branch after PR merge
neonctl branches delete <BRANCH_ID> --project-id <PROJECT_ID>
```

## Running migrations

Migrations live in `src/db/migrations/` as numbered SQL files (`001_init.sql`, etc.).

### Apply manually

```bash theme={null}
# Set connection string
export DATABASE_URL="postgresql://user:pass@host/db?sslmode=require"

# Run a specific migration
psql $DATABASE_URL -f src/db/migrations/001_init.sql

# Run all pending migrations (if using Drizzle Kit)
npx drizzle-kit migrate
```

### Apply via CI

The `migrate.yml` workflow runs on push to `main` and applies migrations automatically using the `DATABASE_URL` GitHub secret.

```yaml theme={null}
- name: Run migrations
  run: npx drizzle-kit migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
```

## Inspecting the schema

```bash theme={null}
# Using Drizzle Kit
npx drizzle-kit introspect

# Or psql
psql $DATABASE_URL -c '\dt'
psql $DATABASE_URL -c '\d+ factory_events'
```

## Factory events table

Each app maintains its own `factory_events` table inside its **own isolated Neon database**. The schema is identical across all apps (shared DDL from the scaffold), but there is no central database — each app writes and queries its own events independently via `@adrper79-dot/analytics`.

A future `factory_core` Neon project will aggregate events across all apps when `factory-admin` is built. Until then, cross-app analytics require querying each app's database separately.

```sql theme={null}
CREATE TABLE IF NOT EXISTS factory_events (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  app_id      TEXT        NOT NULL,
  event       TEXT        NOT NULL,
  user_id     TEXT,
  properties  JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS factory_events_app_id_idx
  ON factory_events (app_id, created_at DESC);
```

## Row-Level Security (RLS)

Factory apps use RLS to isolate tenant data when multiple tenants share a table.

### Enabling RLS

```sql theme={null}
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own tenant's rows
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id', true));
```

### Setting the tenant context per request

```typescript theme={null}
import { createDb, sql } from '@adrper79-dot/neon';

const db = createDb(env.DB);

// Set tenant context before query
await db.execute(sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`);
const rows = await db.execute(sql`SELECT * FROM users`);
```

### Testing RLS in development

```sql theme={null}
-- Simulate tenant A
SET app.tenant_id = 'tenant-a';
SELECT * FROM users; -- should only return tenant-a rows

-- Confirm isolation
SET app.tenant_id = 'tenant-b';
SELECT * FROM users; -- should only return tenant-b rows (or nothing)
```

## Backup and restore

Neon provides continuous branching which doubles as PITR (Point-in-Time Recovery). For explicit backups:

```bash theme={null}
# Dump production
pg_dump $DATABASE_URL > {app}_$(date +%Y%m%d).sql

# Restore to a Neon branch
psql $STAGING_DATABASE_URL < {app}_20250101.sql
```

## Neon connection strings

Connection strings follow this format:

```
postgresql://{user}:{password}@{host}/{database}?sslmode=require
```

Store in GitHub Secrets as `{APP}_CONNECTION_STRING` and pass to Wrangler:

```bash theme={null}
wrangler secret put DATABASE_URL --name {app}
# paste the connection string when prompted
```

## Hyperdrive setup

```bash theme={null}
# Create Hyperdrive binding (one-time)
wrangler hyperdrive create {app}-db --connection-string "$DATABASE_URL"

# Copy the returned ID to wrangler.jsonc
```

```jsonc theme={null}
// wrangler.jsonc
{
  "hyperdrive": [
    {
      "binding": "DB",
      "id": "<hyperdrive-id>"
    }
  ]
}
```