Database

Aphex ships with a PostgreSQL adapter built on Drizzle ORM. Most projects need to do nothing more than set DATABASE_URL and run pnpm db:push. The interface details at the bottom of this page are only relevant if you're writing a custom adapter for a non-Postgres backend.

Quick setup

The base template wires this up for you:

DATABASE_URL=postgres://root:mysecretpassword@localhost:5432/local

pnpm db:start    # docker compose up -d (Postgres + Mailpit)

pnpm db:push     # dev — direct schema sync
# or, for prod:
pnpm db:generate # produces a SQL migration file
pnpm db:migrate  # applies it

What the template wires up

src/lib/server/db/index.ts is the only file you usually touch — it builds a singleton DatabaseAdapter from the Postgres provider and Drizzle schema:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { createPostgreSQLProvider, pgConnectionUrl } from '@aphexcms/postgresql-adapter';
import * as cmsSchema from './cms-schema';
import * as authSchema from './auth-schema';
import type { DatabaseAdapter } from '@aphexcms/cms-core/server';
import { env } from '$env/dynamic/private';

const schema = { ...cmsSchema, ...authSchema };

export const client = postgres(pgConnectionUrl(env), { max: 10 });
export const drizzleDb = drizzle(client, { schema });

const provider = createPostgreSQLProvider({
	client,
	multiTenancy: {
		enableRLS: true, // database-level isolation (default: true)
		enableHierarchy: true // parent → child org reads (default: true)
	}
});

export const db = provider.createAdapter() as DatabaseAdapter;

The Drizzle instance merges CMS tables (documents, assets, schemas, organizations) with auth tables (Better Auth users, sessions, accounts). Both schemas live next to it:

Connection pooling

The default pool is 10 connections. Tune it for your deployment:

export const client = postgres(pgConnectionUrl(env), {
	max: 10, // serverless: keep small (1–3); long-lived: 10–50
	idle_timeout: 20
});

Multi-tenancy

Aphex stores every document and asset against an organizationId. Two layers enforce isolation:

Row-Level Security (RLS)

Before each query the adapter runs:

SET LOCAL app.organization_id = '<uuid>';

The RLS policy then filters rows automatically:

That asymmetry — read down the tree, write only your own — is what enables the parent / child publishing workflows.

System operations

Background jobs, migrations, and seed scripts use systemContext(), which sets app.override_access = true and bypasses RLS:

import { systemContext } from '@aphexcms/cms-core/server';

const docs = await localAPI.collections.post.find(
	systemContext('org-id'), // overrideAccess: true
	{ perspective: 'published' }
);

Single-tenant deployments

If you don't need multi-tenancy, disable both flags. RLS still adds about 0.5–1 ms per query — measurable at scale.

const provider = createPostgreSQLProvider({
	client,
	multiTenancy: {
		enableRLS: false,
		enableHierarchy: false
	}
});

Document lifecycle

Documents follow a draft / published model with hash-based change detection:

publishedHash is a 20-character base36 hash with sorted object keys, so the admin UI can reliably show "you have unpublished changes" without diffing JSON.

Migrations workflow

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
	schema: './src/lib/server/db/schema.ts',
	dialect: 'postgresql',
	dbCredentials: { url: databaseUrl },
	verbose: true,
	strict: true
});

For production, always go through db:generate → review the SQL → db:migrate. db:push will happily drop a column without warning.

Advanced filtering

The adapter translates the where syntax in the Local API directly into JSONB operators:

const posts = await localAPI.collections.post.find(context, {
	where: {
		title: { contains: 'tutorial' },
		status: { equals: 'published' }
	},
	sort: ['-publishedAt'],
	limit: 10,
	depth: 1 // resolve one level of references
});

References are resolved recursively up to a configurable depth (0–5). At depth: 0, references come back as { _ref: 'doc-id' }. At higher depths the referenced document is inlined. Circular references are detected and skipped.

Table reference

You'll only need this when writing custom adapters or doing direct SQL.

cms_documents

cms_assets

Other tables

• cms_document_versions — version history (one row per draft save / publish). See Version History.
• cms_schema_types — registered document and object type definitions (fields stored as JSONB).
• cms_organizations — organizations with parentOrganizationId for hierarchy.
• cms_organization_members — user-to-organization membership with role.
• cms_organization_roles — built-in and custom roles per org with capability arrays.
• cms_invitations — pending org invitations with token and expiry.
• cms_user_sessions — tracks each user's active organization.
• cms_user_profiles — CMS-specific user data (instance role, preferences).
• cms_instance_settings — single-row instance configuration.

Custom adapters

Implement DatabaseAdapter (a composite of specialized interfaces) and pass the result to createCMSConfig({ database }):

interface DatabaseAdapter
	extends
		DocumentAdapter,
		AssetAdapter,
		UserProfileAdapter,
		SchemaAdapter,
		OrganizationAdapter,
		InstanceAdapter {
	connect?(): Promise<void>;
	disconnect?(): Promise<void>;
	isHealthy(): Promise<boolean>;

	// Multi-tenancy (optional)
	initializeRLS?(): Promise<void>;
	hierarchyEnabled: boolean;
	withOrgContext?<T>(organizationId: string, fn: () => Promise<T>): Promise<T>;
	getChildOrganizations(parentOrganizationId: string): Promise<string[]>;

	// First-user detection
	hasAnyUserProfiles?(): Promise<boolean>;
}

Each sub-interface is a focused contract:

The @aphexcms/postgresql-adapter source is the reference implementation — clone it as a starting point.

See also