---
title: "Database"
description: "Work with PostgreSQL through Drizzle, migrations, and row-level security."
order: 5
---

## Overview

Your application runs on PostgreSQL. The browser never talks to the database: it calls your API (`/api/v1/...`), and the API server queries Postgres with [Drizzle](https://orm.drizzle.team). Row-level security (RLS) still guards every table, because each user request runs as a restricted database role that only sees that user's rows.

## Schema Overview

The initial migration (`db/migrations/00001_init.sql`) creates the following tables:

| Table | Purpose |
|-------|---------|
| `organizations` | Multi-tenant orgs with name, slug, and plan (FREE, STARTER, PRO, ENTERPRISE) |
| `memberships` | User-org relationships with role (OWNER, ADMIN, MEMBER) |
| `customers` | Billing customer records, linked to a user or organization (any provider) |
| `subscriptions` | Billing subscription data (status, period, cancellation) |
| `notifications` | System-wide or org-specific notifications with scheduling |
| `notification_dismissals` | Tracks which notifications each user has dismissed |
| `failed_login_attempts` | Brute force protection (email, IP, timestamp) |
| `app_settings` | Key-value store for global config (e.g., MFA enforcement) |
| `cron_job_history` | Background job execution tracking (pg_cron, see [Background Jobs](/docs/background-jobs)) |
| `contact_submissions` | Contact form submissions (name, email, subject, message, status) |
| `newsletter_subscribers` | Newsletter subscribers with double opt-in (email, status, confirmation token) |

## Migrations

SQL migrations live in `db/migrations/`. Run them with:

```bash
npm run db:migrate
```

### Conventions

- **Naming**: Use zero-padded sequential numbering: `00001_init.sql`, `00002_my_feature.sql`
- **RLS**: Always enable RLS on new tables and add policies
- **Timestamps**: Include `created_at` and `updated_at` columns with `TIMESTAMPTZ DEFAULT NOW()`
- **Triggers**: Always add the `update_updated_at()` trigger for auto-updating `updated_at`
- **Indexes**: Add indexes for foreign keys and frequently queried columns

### Creating a New Table

Create a migration file with the table, index, RLS policies, and trigger:

```sql
-- db/migrations/00002_my_table.sql

CREATE TABLE IF NOT EXISTS my_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_my_table_user_id ON my_table(user_id);

-- Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- CRUD policies. TO app_user: the role every user request runs as.
CREATE POLICY "Users can view their own items"
  ON my_table FOR SELECT
  TO app_user
  USING (user_id = (SELECT app.user_id()));

CREATE POLICY "Users can create their own items"
  ON my_table FOR INSERT
  TO app_user
  WITH CHECK (user_id = (SELECT app.user_id()));

CREATE POLICY "Users can update their own items"
  ON my_table FOR UPDATE
  TO app_user
  USING (user_id = (SELECT app.user_id()))
  WITH CHECK (user_id = (SELECT app.user_id()));

CREATE POLICY "Users can delete their own items"
  ON my_table FOR DELETE
  TO app_user
  USING (user_id = (SELECT app.user_id()));

-- Auto-update updated_at
CREATE TRIGGER my_table_updated_at
  BEFORE UPDATE ON my_table
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();
```

### Migrating a live app

Your app applies pending migrations itself when it starts, while the previous version may still be serving traffic. Three habits keep that safe:

- **Set a lock timeout on busy tables.** Changing a table locks it. If a long query is already using the table, your migration waits for it, and every new query on that table waits behind your migration, so the app stalls. Start a migration that touches a busy table with `SET LOCAL lock_timeout = '5s';`. If the lock does not come in time, the migration fails cleanly, nothing is applied, and you can deploy again at a quieter moment.
- **Expand, then contract.** During a rollout, old and new versions of your app run side by side against the new schema. So never drop or rename a column the old version still reads in the same deploy. Add the new column first and ship code that handles both, then remove the old column in a later deploy.
- **Keep each migration short.** A deploy waits about 10 minutes on Kubernetes, and about 2.5 minutes on Docker Compose, for migrations to finish. A longer migration is still running when the deploy reports failure. Backfill big tables in batches (a script or a [background job](/docs/background-jobs)) rather than in one migration.

## Row-Level Security

Every table has RLS enabled. Here is how a request reaches it: your route calls `withUserDb(user.id, ...)`, which opens a transaction, switches to the `app_user` role, and sets `app.user_id` to the signed-in user's id (empty for an anonymous visitor). Policies read that id back with `app.user_id()`, which returns the user's UUID, or `NULL` when nobody is signed in.

A few rules keep policies correct:

- Write `(SELECT app.user_id())`, not bare `app.user_id()`, so Postgres evaluates it once per statement instead of once per row.
- Give every `UPDATE` policy a `WITH CHECK` that mirrors its `USING`. `USING` decides which rows you may change, `WITH CHECK` decides what they may become, and Postgres checks them separately.
- Target policies `TO app_user`, the role user requests run as.

Policies also use helper functions defined in the initial migration:

### RLS Helper Functions

| Function | Returns | Description |
|----------|---------|-------------|
| `get_user_org_ids()` | `SETOF UUID` | Org IDs where the user is a member (any role) |
| `get_user_admin_org_ids()` | `SETOF UUID` | Org IDs where the user is OWNER or ADMIN |
| `get_user_owner_org_ids()` | `SETOF UUID` | Org IDs where the user is OWNER |
| `get_user_customer_ids()` | `SETOF UUID` | Customer IDs for the user or their orgs |
| `is_super_admin()` | `BOOLEAN` | `true` if the user's `"user".role` is `super_admin` |

All helper functions use `SECURITY DEFINER` so they can read the `memberships` table regardless of the calling user's permissions.

### Common Policy Patterns

**User-owned data**: rows belong to a specific user:

```sql
CREATE POLICY "Users can view their own items"
  ON my_table FOR SELECT
  TO app_user
  USING (user_id = (SELECT app.user_id()));
```

**Organization data**: rows belong to an org the user is a member of:

```sql
CREATE POLICY "Members can view org data"
  ON my_table FOR SELECT
  TO app_user
  USING (organization_id IN (SELECT get_user_org_ids()));
```

**Admin-only writes**: only org owners/admins can modify:

```sql
CREATE POLICY "Admins can update org data"
  ON my_table FOR UPDATE
  TO app_user
  USING (organization_id IN (SELECT get_user_admin_org_ids()))
  WITH CHECK (organization_id IN (SELECT get_user_admin_org_ids()));
```

**Super admin access**: full access for super admins:

```sql
CREATE POLICY "Super admins can do anything"
  ON my_table FOR ALL
  TO app_user
  USING (is_super_admin())
  WITH CHECK (is_super_admin());
```

## Server-Side Queries

The API server talks to Postgres directly with [Drizzle](https://orm.drizzle.team). Tables are defined in `src/server/db/schema.ts` (add yours there after its migration), and every query goes through `src/server/db/client.ts`, which gives you two ways in:

- **`withUserDb(userId, fn)`** runs `fn` as the signed-in user (pass `null` for an anonymous visitor). RLS applies to every statement inside, so a forgotten `where` still cannot leak another user's rows. Use this by default.
- **`adminDb`** runs as the server itself and bypasses RLS. Use it only for work no user is behind (webhooks, admin tools, background jobs), and check yourself that the caller may touch the rows first.

```typescript
import { eq } from 'drizzle-orm';
import { adminDb, withUserDb } from '../../db/client';
import { myTable } from '../../db/schema';

// As the signed-in user (row-level security applies). Use this by default.
const rows = await withUserDb(user.id, (tx) =>
  tx.select().from(myTable).where(eq(myTable.organizationId, orgId))
);

// As the server itself (bypasses RLS): webhooks, admin tools, jobs only,
// and only after you have checked the caller may touch these rows.
const all = await adminDb.select().from(myTable);
```

Values you pass to Drizzle are sent as query parameters, never pasted into the SQL, so input cannot change the query. Keep it that way: never build SQL text from user input.

### Exposing data to the browser

The browser gets data by calling one of your own `/api/v1` routes. Add a route in `src/server/routes/v1/`, check the user, and query through `withUserDb`:

```typescript
// src/server/routes/v1/items.ts
import { Hono } from 'hono';
import { z } from 'zod';
import { withUserDb } from '../../db/client';
import { myTable } from '../../db/schema';
import { sanitizeError } from '../../lib/errors';
import type { HonoVariables } from '../../types';

export const itemRoutes = new Hono<{ Variables: HonoVariables }>();

itemRoutes.get('/', async (c) => {
  const user = c.get('user');
  if (!user) return c.json({ error: 'Unauthorized' }, 401);

  try {
    // RLS returns only this user's rows; no where clause needed for safety.
    const items = await withUserDb(user.id, (tx) => tx.select().from(myTable));
    return c.json({ items });
  } catch (error) {
    return c.json({ error: sanitizeError(error, 'Failed to fetch items') }, 500);
  }
});

const createItem = z.object({ name: z.string().min(1).max(200) });

itemRoutes.post('/', async (c) => {
  const user = c.get('user');
  if (!user) return c.json({ error: 'Unauthorized' }, 401);

  const parsed = createItem.safeParse(await c.req.json().catch(() => null));
  if (!parsed.success) return c.json({ error: 'Invalid item' }, 400);

  try {
    // The INSERT policy's WITH CHECK rejects a user_id that is not the caller's.
    const [item] = await withUserDb(user.id, (tx) =>
      tx.insert(myTable).values({ userId: user.id, name: parsed.data.name }).returning()
    );
    return c.json({ item }, 201);
  } catch (error) {
    return c.json({ error: sanitizeError(error, 'Failed to create item') }, 500);
  }
});
```

Mount it in `src/server/index.ts` with `app.route('/api/v1/items', itemRoutes)`.

## TanStack Query

Use TanStack Query for client-side data fetching with caching, deduplication, and automatic refetching. `apiJson` from `src/client/lib/api.ts` calls your API with the user's session token attached, and throws on an error response so TanStack Query sees it.

### Fetching Data

```typescript
import { useQuery } from '@tanstack/react-query';
import { apiJson } from '@/lib/api';

interface Item {
  id: string;
  name: string;
}

export function useItems() {
  return useQuery({
    queryKey: ['items'],
    queryFn: async () => {
      const { items } = await apiJson<{ items: Item[] }>('/api/v1/items');
      return items;
    },
  });
}
```

### Mutations with Cache Invalidation

```typescript
import { useMutation, useQueryClient } from '@tanstack/react-query';
import { apiJson } from '@/lib/api';

export function useCreateItem() {
  const queryClient = useQueryClient();

  return useMutation({
    mutationFn: async (item: { name: string }) => {
      const { item: created } = await apiJson<{ item: Item }>('/api/v1/items', {
        method: 'POST',
        body: item,
      });
      return created;
    },
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: ['items'] });
    },
  });
}
```

### Usage in Components

```tsx
function ItemList() {
  const { data: items, isLoading, error } = useItems();
  const createItem = useCreateItem();

  if (isLoading) return <p>Loading...</p>;
  if (error) return <p>Error: {error.message}</p>;

  return (
    <div>
      {items?.map((item) => <p key={item.id}>{item.name}</p>)}
      <button onClick={() => createItem.mutate({ name: 'New item' })}>
        Add Item
      </button>
    </div>
  );
}
```
