Database Architecture

Internal Schema: _postbase

All Postbase platform tables live in the _postbase PostgreSQL schema, managed by Drizzle ORM.

• organisations — Groups of projects

• projects — Individual Postbase instances

• users — Per-project authenticated users

• sessions — Refresh token store

• provider_configs — Per-project OAuth provider settings

• email_settings — Per-project SMTP configuration

• email_templates — Per-project email templates (magic_link, etc.)

• storage_buckets — Bucket definitions

• storage_objects — Files within buckets

• storage_connections — Per-project S3/R2/GCS credentials

• api_keys — Anon + service role keys per project

• audit_logs — Action audit trail

Per-Project Schema Isolation

Each project gets its own PostgreSQL schema for user-created tables:

Schema name: proj_<projectId_without_hyphens>
Example:     proj_550e8400e29b41d4a716446655440000

Isolation Rules

• User tables — PostgreSQL schema proj_<id>

• Cron jobs — Named pb_<id>_<jobName> in cron.job

• Message queues — Named pb_<id>_<queueName> in pgmq

• RLS policies — Scoped to each project's schema tables

ORM Setup

# Generate Drizzle migrations
pnpm drizzle-kit generate

# Push schema directly (dev)
pnpm drizzle-kit push

import { db } from '@/lib/db'
import { projects, users } from '@/lib/db/schema'
import { eq } from 'drizzle-orm'

const [project] = await db
  .select()
  .from(projects)
  .where(eq(projects.id, projectId))
  .limit(1)