Your application's database

Every Aha! Builder application is backed by its own PostgreSQL database. Your application defines tables, queries and updates them through a type-safe ORM, and evolves its structure over time with migration files. Elle creates and changes this structure as you describe the data your application needs to store.

This reference documents how the database is accessed: the Drizzle ORM connection, query operators, and the rules for writing migrations. Read it to understand how your application's data is stored and changed, to review what Elle generated, or to ground an AI assistant in the specifics — reference this article with Elle or any LLM so it knows exactly how the database works in Aha! Builder.

Click any of the following links to skip ahead:

• Overview

• Schema types

• Enums

• Migrations

• Rules

• Creating a new migration

• What if I need to undo a change?

• Aha! user ID columns must use TEXT

• Build it with Elle

Overview

Drizzle ORM is used for database access.

The active database connection is imported from builder-core.

import { db } from '@aha-app/builder-core';
import { eq } from 'drizzle-orm';
import { customersTable } from './schema.ts';

...

const customers = await db.select().from(customersTable).where(eq(customersTable.name, 'Chris'));

Top

Schema types

Export Drizzle-inferred select and insert types next to every table in db/schema.ts.

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

export const customersTable = pgTable('customers', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email'),
  createdAt: timestamp('created_at').defaultNow(),
});

export type Customer = typeof customersTable.$inferSelect;
export type NewCustomer = typeof customersTable.$inferInsert;

Use Customer for the rows that db.select() and .returning() give back. Use NewCustomer to build server function input types for inserts and updates, usually with Pick, Partial, and ID types from the select model.

import {
  customersTable,
  type Customer,
  type NewCustomer,
} from '@/db/schema';

type CreateCustomerInput = Pick<NewCustomer, 'name' | 'email'>;
type UpdateCustomerInput = {
  id: Customer['id'];
} & Partial<Pick<NewCustomer, 'name' | 'email'>>;

const imported: Customer[] = [];

Prefer these inferred types over hand-written row shapes. Do not use any for database rows when a schema type exists.

When a server function returns database rows, define it as an exported named handler and annotate the return type — Promise<Customer>, Promise<Customer[]>, Promise<Customer | null>. Register the handler by identifier, such as server.data('getCustomers', getCustomers). The generated @/server functions and hooks then infer their frontend types from the exported handler.

Top

Enums

Use PostgreSQL enums for constrained domain values such as status, stage, priority, type, category, or condition. Do not model these as plain text columns when you already know the allowed values.

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

export const customerStatusEnum = pgEnum('customer_status', [
  'lead',
  'prospect',
  'customer',
  'inactive',
]);

export const customersTable = pgTable('customers', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  status: customerStatusEnum('status').notNull().default('lead'),
  createdAt: timestamp('created_at').defaultNow(),
});

export type Customer = typeof customersTable.$inferSelect;
export type NewCustomer = typeof customersTable.$inferInsert;
export type CustomerStatus = (typeof customerStatusEnum.enumValues)[number];

Create the enum type in the migration before you use it in a table:

CREATE TYPE customer_status AS ENUM ('lead', 'prospect', 'customer', 'inactive');

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  status customer_status NOT NULL DEFAULT 'lead',
  created_at TIMESTAMP DEFAULT NOW()
);

To convert an existing TEXT column, write a new migration that creates the enum type, normalizes any invalid values, then casts the column with USING status::customer_status.

Top

Migrations

SQL migration files live in db/migrations/ and are executed in sorted filename order. Each migration runs only once — the system tracks which files have been executed and skips them on subsequent runs.

Rules

• Never edit an existing migration. Once a migration has been executed, its filename is recorded. Editing it will not cause it to re-run. If you need to change a table, create a new migration file.

• Use sequential numbered prefixes for ordering: 001_create_users.sql, 002_add_email_to_users.sql, etc.

• Do not use IF NOT EXISTS — migrations run exactly once, so existence checks are unnecessary.

Creating a new migration

To modify an existing table, create a new migration file with the next number:

db/migrations/003_add_role_to_users.sql:

ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'member';

What if I need to undo a change?

Create a new migration that reverses the change:

db/migrations/004_remove_role_from_users.sql:

ALTER TABLE users DROP COLUMN role;

Top

Aha! user ID columns must use TEXT

The auth_identifier column in the users table stores the Aha! platform user ID as TEXT. If you add additional columns that reference Aha! platform IDs, use TEXT as well.

The local users.id column is a normal SERIAL integer. Use integer for foreign keys that reference it.

Top