Put rules in the schema

Application validation gives friendly errors. Database constraints protect the data when another path forgets that validation.

Keep required fields notNull(), make email unique, and choose delete behavior deliberately. Add an index only for a query pattern you can name. For our app, listing one user’s notes by creation time is that pattern.

import { index, int, sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { sql } from 'drizzle-orm'

export const notes = sqliteTable('notes', {
  id: int().primaryKey({ autoIncrement: true }),
  authorId: int('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text().notNull(),
  body: text().notNull().default(''),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
}, table => [
  index('notes_author_created_idx').on(table.authorId, table.createdAt),
])

Cascade delete means deleting a user deletes their notes. That may fit a disposable notes app and be unacceptable for invoices or audit records. The ORM cannot make this product decision for you.

Every index speeds some reads and adds work to writes. Start from a real filter and sort order, then inspect the plan after data exists.

Write one sentence justifying every constraint and the composite index. Remove the index temporarily and compare the query plan for one author’s newest notes after inserting enough rows to make the difference visible.