Relational Database — Docs

Drizzle ORM schema for relational data and Better Auth integration.

Provider: PostgreSQL via Neon (serverless) or any PostgreSQL 16+ host.

Overview

Table	Purpose	Source
`user`	User accounts	Better Auth (auto-generated)
`session`	Auth sessions	Better Auth (auto-generated)
`account`	OAuth provider links	Better Auth (auto-generated)
`verification`	Magic link/OTP tokens	Better Auth (auto-generated)
`user_profile`	User Profile Data (public)	Custom
`user_settings`	Preferences & Settings	Custom
`items`	Generic entities	Custom
`tags`	Categorization	Custom
`item_tags`	Item-tag relations	Custom
`files`	Upload references	Custom

Better Auth Tables

Better Auth auto-generates its schema via CLI. These tables are not manually edited.

Generation

bunx @better-auth/cli generate
bunx drizzle-kit migrate

Schema Reference

The generated tables live in a separate file with _ prefix to indicate auto-generation:

// src/lib/server/db/schema/_better-auth.ts
// AUTO-GENERATED by `bunx @better-auth/cli generate`
// Do not edit manually. Re-run CLI after Better Auth updates.

import { pgTable, text, boolean, timestamp } from 'drizzle-orm/pg-core';

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  name: text('name'),
  image: text('image'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// CRITICAL: Indexes MUST be defined inside the pgTable callback (3rd parameter).
// Standalone index() statements outside pgTable DO NOT work - drizzle-kit ignores them.
export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
}, (table) => [
  // REQUIRED: Better Auth does NOT auto-generate indexes. Add manually for performance.
  // Without these, session lookups become bottlenecks at scale.
  index('session_user_id_idx').on(table.userId),
  index('session_expires_at_idx').on(table.expiresAt),
]);

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),
  providerId: text('provider_id').notNull(),
  providerUserId: text('provider_user_id').notNull(),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  expiresAt: timestamp('expires_at', { withTimezone: true }),
});

export const verification = pgTable('verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
});

File Handling

Approach	Pros	Cons
Commit the file	Type inference works, no extra step	Must re-run CLI on BA updates
Gitignore + generate on install	Always fresh	Needs post-install hook

Recommended: Commit the file with _ prefix. Add to CI to verify it's up-to-date.

Entity Relationship Diagram

┌─────────────────┐       ┌─────────────────┐
│      user       │       │    session      │  (Better Auth)
├─────────────────┤       ├─────────────────┤
│ id PK           │◀──────│ userId FK       │
│ email           │       │ id PK           │
│ emailVerified   │       │ token           │
│ name            │       │ expiresAt       │
│ image           │       │ ipAddress       │
│ createdAt       │       │ userAgent       │
│ updatedAt       │       └─────────────────┘
└────────┬────────┘
         │                ┌─────────────────┐
         │◀───────────────│    account      │  (Better Auth)
         │                ├─────────────────┤
         │                │ id PK           │
         │                │ userId FK       │
         │                │ providerId      │
         │                │ providerUserId  │
         │                │ accessToken     │
         │                │ refreshToken    │
         │                └─────────────────┘
         │
         │                ┌─────────────────┐
         │◀───────────────│  user_profile   │  (Custom)
         │                ├─────────────────┤
         │                │ userId PK/FK    │
         │                │ bio             │
         │                │ website         │
         │                │ timezone        │
         │                └─────────────────┘
         │
         │                ┌─────────────────┐
         │◀───────────────│ user_settings   │  (Custom)
         │                ├─────────────────┤
         │                │ userId PK/FK    │
         │                │ theme           │
         │                │ language        │
         │                │ notifications   │
         │                └─────────────────┘
         │
         │  ┌─────────────────┐
         └─▶│     items       │
            ├─────────────────┤
            │ id PK           │◀─┐
            │ userId FK       │  │
            │ title           │  │     ┌─────────────────┐
            │ description     │  │     │   item_tags     │
            │ content         │  │     ├─────────────────┤
            │ status          │  └─────│ itemId FK       │
            │ imageUrl        │        │ tagId FK        │──┐
            │ createdAt       │        └─────────────────┘  │
            │ updatedAt       │                             │
            └────────┬────────┘        ┌─────────────────┐  │
                     │                 │      tags       │  │
                     │                 ├─────────────────┤  │
                     ▼                 │ id PK           │◀─┘
            ┌─────────────────┐        │ name            │
            │     files       │        │ color           │
            ├─────────────────┤        │ createdAt       │
            │ id PK           │        └─────────────────┘
            │ userId FK       │
            │ itemId FK?      │
            │ filename        │
            │ storageKey      │
            │ url             │
            │ size            │
            │ mimeType        │
            │ createdAt       │
            └─────────────────┘

Custom Tables

User Extensions

// src/lib/server/db/schema/auth.ts
import { pgTable, text } from 'drizzle-orm/pg-core';
import { user } from './_better-auth';

export const userProfile = pgTable('user_profile', {
  userId: text('user_id')
    .primaryKey()
    .references(() => user.id, { onDelete: 'cascade' }),
  bio: text('bio'),
  website: text('website'),
  timezone: text('timezone').default('UTC'),
});

User Settings (Preferences & Settings)

Contains both Preferences (UX customization like theme, language) and Settings (feature toggles like notifications). See ../../foundation/user-data.md for terminology.

// src/lib/server/db/schema/settings.ts
import { pgTable, text, boolean, pgEnum } from 'drizzle-orm/pg-core';
import { user } from './_better-auth';

export const themeEnum = pgEnum('theme', ['light', 'dark', 'system']);

export const userSettings = pgTable('user_settings', {
  userId: text('user_id')
    .primaryKey()
    .references(() => user.id, { onDelete: 'cascade' }),
  // Preferences (UX customization)
  theme: themeEnum('theme').notNull().default('system'),
  language: text('language').notNull().default('en'),
  // Settings (feature configuration)
  notificationsEnabled: boolean('notifications_enabled').notNull().default(true)
});

Items & Tags

// src/lib/server/db/schema/items.ts
import { pgTable, text, timestamp, pgEnum, primaryKey } from 'drizzle-orm/pg-core';
import { user } from './_better-auth';

export const itemStatusEnum = pgEnum('item_status', ['draft', 'published', 'archived']);

export const items = pgTable('items', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  description: text('description'),
  content: text('content'),
  status: itemStatusEnum('status').notNull().default('draft'),
  imageUrl: text('image_url'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow()
});

export const tags = pgTable('tags', {
  id: text('id').primaryKey(),
  name: text('name').notNull().unique(),
  color: text('color').notNull().default('#6b7280'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
});

export const itemTags = pgTable(
  'item_tags',
  {
    itemId: text('item_id')
      .notNull()
      .references(() => items.id, { onDelete: 'cascade' }),
    tagId: text('tag_id')
      .notNull()
      .references(() => tags.id, { onDelete: 'cascade' })
  },
  (table) => ({
    pk: primaryKey({ columns: [table.itemId, table.tagId] })
  })
);

Files

// src/lib/server/db/schema/files.ts
import { pgTable, text, timestamp, integer } from 'drizzle-orm/pg-core';
import { user } from './_better-auth';
import { items } from './items';

export const files = pgTable('files', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  itemId: text('item_id').references(() => items.id, { onDelete: 'set null' }),
  filename: text('filename').notNull(),
  storageKey: text('storage_key').notNull(),
  url: text('url'),
  size: integer('size').notNull(),
  mimeType: text('mime_type').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
});

Schema Index

// src/lib/server/db/schema/index.ts
export * from './_better-auth'; // Auto-generated: user, session, account, verification
export * from './auth';         // Custom: userProfile
export * from './settings';     // Custom: userSettings
export * from './items';        // Custom: items, tags, itemTags
export * from './files';        // Custom: files

ID Generation

Prefixed IDs for debugging clarity:

// src/lib/server/db/id.ts
import { nanoid } from 'nanoid';

export const createId = {
  user: () => `usr_${nanoid(12)}`,
  session: () => `ses_${nanoid(24)}`,
  item: () => `itm_${nanoid(12)}`,
  tag: () => `tag_${nanoid(8)}`,
  file: () => `fil_${nanoid(12)}`,
  token: () => nanoid(32)
};

// Usage:
// const user = { id: createId.user(), ... }
// Results in: 'usr_V1StGXR8_Z5j'

Relations

// src/lib/server/db/relations.ts
import { relations } from 'drizzle-orm';
import { user, session, account } from './schema/_better-auth';
import { userProfile } from './schema/auth';
import { userSettings } from './schema/settings';
import { items, tags, itemTags } from './schema/items';
import { files } from './schema/files';

export const userRelations = relations(user, ({ one, many }) => ({
  profile: one(userProfile, {
    fields: [user.id],
    references: [userProfile.userId]
  }),
  settings: one(userSettings, {
    fields: [user.id],
    references: [userSettings.userId]
  }),
  sessions: many(session),
  accounts: many(account),
  items: many(items),
  files: many(files)
}));

export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, {
    fields: [session.userId],
    references: [user.id]
  })
}));

export const accountRelations = relations(account, ({ one }) => ({
  user: one(user, {
    fields: [account.userId],
    references: [user.id]
  })
}));

export const itemsRelations = relations(items, ({ one, many }) => ({
  user: one(user, {
    fields: [items.userId],
    references: [user.id]
  }),
  files: many(files),
  itemTags: many(itemTags)
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  itemTags: many(itemTags)
}));

export const itemTagsRelations = relations(itemTags, ({ one }) => ({
  item: one(items, {
    fields: [itemTags.itemId],
    references: [items.id]
  }),
  tag: one(tags, {
    fields: [itemTags.tagId],
    references: [tags.id]
  })
}));

export const filesRelations = relations(files, ({ one }) => ({
  user: one(user, {
    fields: [files.userId],
    references: [user.id]
  }),
  item: one(items, {
    fields: [files.itemId],
    references: [items.id]
  })
}));

Type Inference

// src/lib/server/db/types.ts
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';
import { user, session, account } from './schema/_better-auth';
import { userProfile } from './schema/auth';
import { userSettings } from './schema/settings';
import { items, tags, files } from './schema';

// Better Auth types
export type User = InferSelectModel<typeof user>;
export type Session = InferSelectModel<typeof session>;
export type Account = InferSelectModel<typeof account>;

// Custom types
export type UserProfile = InferSelectModel<typeof userProfile>;
export type UserSettings = InferSelectModel<typeof userSettings>;
export type Item = InferSelectModel<typeof items>;
export type Tag = InferSelectModel<typeof tags>;
export type File = InferSelectModel<typeof files>;

// Insert types
export type NewUserProfile = InferInsertModel<typeof userProfile>;
export type NewItem = InferInsertModel<typeof items>;
export type NewTag = InferInsertModel<typeof tags>;
export type NewFile = InferInsertModel<typeof files>;

// Composite types
export type ItemWithTags = Item & { tags: Tag[] };
export type ItemWithFiles = Item & { files: File[] };
export type ItemFull = Item & { tags: Tag[]; files: File[]; user: User };
export type UserWithProfile = User & { profile: UserProfile | null };

Type Flow: Database → Load Function → Component

Limitation: InferSelectModel doesn't include Drizzle relations. You must manually type queries that use .with().

// src/lib/server/db/queries/items.ts
import { db } from '$lib/server/db';
import { items, tags, itemTags } from '$lib/server/db/schema';
import { eq } from 'drizzle-orm';

// Query function with typed return
export async function getItemsWithTags(userId: string) {
  return db.query.items.findMany({
    where: eq(items.userId, userId),
    with: {
      itemTags: {
        with: { tag: true }
      }
    }
  });
}

// Infer return type from the function
export type ItemsWithTags = Awaited<ReturnType<typeof getItemsWithTags>>;

// src/routes/showcase/data/+page.server.ts
import { getItemsWithTags } from '$lib/server/db/queries/items';
import type { PageServerLoad } from './$types';

export const load: PageServerLoad = async ({ locals }) => {
  const items = await getItemsWithTags(locals.user!.id);
  return { items }; // Fully typed: ItemsWithTags
};

<!-- src/routes/showcase/data/+page.svelte -->
<script lang="ts">
  let { data } = $props();
  // data.items is fully typed with relations!
  const items = $derived(data.items);
</script>

{#each items as item}
  <p>{item.title}</p>
  {#each item.itemTags as itemTag}
    <span>{itemTag.tag.name}</span>
  {/each}
{/each}

Database Client

// src/lib/server/db/index.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import * as schema from './schema';
import * as relations from './relations';

const sql = neon(process.env.DATABASE_URL!);

export const db = drizzle(sql, {
  schema: { ...schema, ...relations }
});

export { schema };
export * from './types';
export * from './id';

Indexes

// Add to respective schema files
import { index } from 'drizzle-orm/pg-core';

// items - user's items, status filter, recent first
export const itemsUserIdx = index('items_user_id_idx').on(items.userId);
export const itemsStatusIdx = index('items_status_idx').on(items.status);
export const itemsCreatedIdx = index('items_created_at_idx').on(items.createdAt);

// files - user's files, item attachments
export const filesUserIdx = index('files_user_id_idx').on(files.userId);
export const filesItemIdx = index('files_item_id_idx').on(files.itemId);

Migrations

Configuration

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/lib/server/db/schema',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!
  }
});

Commands

# Generate migration from schema changes
bunx drizzle-kit generate

# Apply migrations
bunx drizzle-kit migrate

# Push directly (dev only, destructive)
bunx drizzle-kit push

# Open Drizzle Studio
bunx drizzle-kit studio

Transactions

Use db.transaction() for operations that must be atomic.

Form Action with Transaction

// src/routes/showcase/data/new/+page.server.ts
import { fail, redirect } from '@sveltejs/kit';
import { superValidate } from 'sveltekit-superforms';
import { valibot } from 'sveltekit-superforms/adapters';
import { db, createId, schema } from '$lib/server/db';
import { itemSchema } from '$lib/schemas/item';

export const actions = {
  default: async ({ request, locals }) => {
    const form = await superValidate(request, valibot(itemSchema));
    if (!form.valid) return fail(400, { form });

    await db.transaction(async (tx) => {
      // 1. Create item
      const [item] = await tx.insert(schema.items).values({
        id: createId.item(),
        userId: locals.user!.id,
        title: form.data.title,
        description: form.data.description,
        status: 'draft',
      }).returning();

      // 2. Create item-tag associations
      if (form.data.tagIds?.length) {
        await tx.insert(schema.itemTags).values(
          form.data.tagIds.map(tagId => ({
            itemId: item.id,
            tagId,
          }))
        );
      }

      // 3. Outbox event for graph sync (optional)
      await tx.insert(schema.outbox).values({
        id: `obx_${nanoid(12)}`,
        aggregateType: 'item',
        aggregateId: item.id,
        eventType: 'created',
        payload: { itemId: item.id, userId: locals.user!.id },
      });
    });

    redirect(303, '/showcase/data');
  },
};

When to Use Transactions

Use Case	Transaction Needed?
Single insert/update	No
Insert + related records	Yes
Insert + outbox event	Yes
Read-only queries	No
Conditional update (check then write)	Yes

Error Handling

Postgres Error Code Mapping

// src/lib/server/db/errors.ts
import { error } from '@sveltejs/kit';

export const PG_ERROR_CODES = {
  UNIQUE_VIOLATION: '23505',
  FOREIGN_KEY_VIOLATION: '23503',
  NOT_NULL_VIOLATION: '23502',
  CHECK_VIOLATION: '23514',
  QUERY_CANCELED: '57014',
} as const;

export function mapDbError(err: unknown): never {
  // Check if it's a Postgres error with a code
  if (err instanceof Error && 'code' in err) {
    const code = (err as { code: string }).code;

    switch (code) {
      case PG_ERROR_CODES.UNIQUE_VIOLATION:
        error(409, 'A record with this value already exists.');
      case PG_ERROR_CODES.FOREIGN_KEY_VIOLATION:
        error(400, 'Referenced record does not exist.');
      case PG_ERROR_CODES.NOT_NULL_VIOLATION:
        error(400, 'Required field is missing.');
      case PG_ERROR_CODES.CHECK_VIOLATION:
        error(400, 'Invalid value provided.');
      case PG_ERROR_CODES.QUERY_CANCELED:
        error(408, 'Request timeout.');
      default:
        console.error('Database error:', err);
        error(500, 'Database error occurred.');
    }
  }

  console.error('Unexpected error:', err);
  error(500, 'An unexpected error occurred.');
}

Usage in Load Functions

// src/routes/showcase/data/[id]/+page.server.ts
import { mapDbError } from '$lib/server/db/errors';

export async function load({ params }) {
  try {
    const item = await db.query.items.findFirst({
      where: eq(items.id, params.id),
      with: { itemTags: { with: { tag: true } } }
    });

    if (!item) error(404, 'Item not found');
    return { item };
  } catch (err) {
    mapDbError(err);
  }
}

Seed Data

// scripts/seed.ts
import { db, createId, schema } from '$lib/server/db';

async function seed() {
  // Create test user
  const userId = createId.user();
  await db.insert(schema.user).values({
    id: userId,
    email: 'demo@example.com',
    name: 'Demo User',
    emailVerified: true
  });

  // Create user settings
  await db.insert(schema.userSettings).values({
    userId,
    theme: 'system',
    language: 'en'
  });

  // Create tags
  const tagData = [
    { id: createId.tag(), name: 'Important', color: '#ef4444' },
    { id: createId.tag(), name: 'Work', color: '#3b82f6' },
    { id: createId.tag(), name: 'Personal', color: '#22c55e' }
  ];
  await db.insert(schema.tags).values(tagData);

  // Create items
  const itemData = [
    {
      id: createId.item(),
      userId,
      title: 'First Item',
      description: 'This is a sample item for testing.',
      status: 'published' as const
    },
    {
      id: createId.item(),
      userId,
      title: 'Draft Item',
      description: 'This item is still a draft.',
      status: 'draft' as const
    }
  ];
  await db.insert(schema.items).values(itemData);

  // Link items to tags
  await db.insert(schema.itemTags).values([
    { itemId: itemData[0].id, tagId: tagData[0].id },
    { itemId: itemData[0].id, tagId: tagData[1].id },
    { itemId: itemData[1].id, tagId: tagData[2].id }
  ]);

  console.log('Seed complete');
}

seed();

File Structure

src/lib/server/db/
├── index.ts              # DB client export
├── id.ts                 # ID generation
├── types.ts              # Type inference
├── relations.ts          # Drizzle relations
└── schema/
    ├── index.ts          # Re-exports
    ├── _better-auth.ts   # Auto-generated (underscore = generated)
    ├── auth.ts           # Custom: userProfile
    ├── settings.ts       # Custom: userSettings
    ├── items.ts          # Custom: items, tags, itemTags
    └── files.ts          # Custom: files

drizzle/
├── 0000_better_auth.sql  # Better Auth generated migration
├── 0001_custom.sql       # Custom tables migration
└── meta/                 # Migration metadata

scripts/
└── seed.ts               # Seed data

../../foundation/user-data.md - Data category definitions
graph.md - Neo4j for relationship data
../auth.md - Better Auth session management
../api.md - REST endpoints querying this schema