Prompting the Database Schema with AI

Objectives

By the end of this chapter, you should be able to:

• Write a prompt that gets an AI coding assistant to model a Drizzle schema with real database-level invariants, not just columns
• Catch an AI assistant sizing a column for the spec instead of the real-world data it will actually hold
• Recognize the difference between a plain unique index and a partial one, and know which one your prompt actually needs
• Verify a generated migration by reading the SQL and testing the constraint by hand, not by trusting a confident summary

💡 Why this matters: A schema mistake is the most expensive kind of AI mistake to catch late. Code that renders wrong is obvious the moment you look at the screen. A missing constraint is invisible until the exact write path that needed it runs in production, months from now. This is the lesson where “looks right” and “is right” matter most.

Invariants Are the Part an AI Assistant Will Skip

Ask an AI assistant for “a Postgres schema for a flight tracker” and you’ll get tables, columns, and types. What you probably will not get, unless you ask for it directly, is the database enforcing “exactly one home location” or “at most one default watch region.” Those are the two invariants the original SKYWATCH app pushes into the database instead of trusting application code to honor them on every write path, and they’re also exactly the kind of requirement an AI assistant has no way to infer from a one-line request. It will happily model home_location as a normal table with a serial primary key and let your app code decide how many rows to allow.

So the prompt below states both invariants explicitly, by name, the same way you’d state them to a human engineer who hasn’t seen the rest of the app yet.

The Prompt

What It Built

// apps/server/drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL ?? "postgresql://skywatch:skywatch@localhost:5432/skywatch",
  },
  strict: true,
  verbose: true,
});

// apps/server/src/db/client.ts
import { Pool } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { env } from "../env.js";
import * as schema from "./schema.js";

export const pool = new Pool({ connectionString: env.DATABASE_URL });
export const db = drizzle(pool, { schema });

// apps/server/src/db/schema.ts
import { sql } from "drizzle-orm";
import {
  pgTable, serial, varchar, doublePrecision, integer, boolean, timestamp, index, check, uniqueIndex,
} from "drizzle-orm/pg-core";

export const positionSnapshots = pgTable(
  "position_snapshots",
  {
    id: serial("id").primaryKey(),
    hex: varchar("hex", { length: 10 }).notNull(),
    flight: varchar("flight", { length: 10 }),
    registration: varchar("registration", { length: 10 }),
    typeCode: varchar("type_code", { length: 8 }),
    lat: doublePrecision("lat").notNull(),
    lon: doublePrecision("lon").notNull(),
    altBaroFt: integer("alt_baro_ft"),
    onGround: boolean("on_ground").notNull().default(false),
    groundSpeedKt: doublePrecision("ground_speed_kt"),
    trackDeg: doublePrecision("track_deg"),
    verticalRateFpm: integer("vertical_rate_fpm"),
    squawk: varchar("squawk", { length: 4 }),
    category: varchar("category", { length: 4 }),
    recordedAt: timestamp("recorded_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index("position_snapshots_hex_recorded_at_idx").on(t.hex, t.recordedAt),
    index("position_snapshots_recorded_at_idx").on(t.recordedAt),
  ]
);

export const squawkAlerts = pgTable(
  "squawk_alerts",
  {
    id: serial("id").primaryKey(),
    hex: varchar("hex", { length: 10 }).notNull(),
    flight: varchar("flight", { length: 10 }),
    squawk: varchar("squawk", { length: 4 }).notNull(),
    lat: doublePrecision("lat"),
    lon: doublePrecision("lon"),
    altBaroFt: integer("alt_baro_ft"),
    detectedAt: timestamp("detected_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("squawk_alerts_detected_at_idx").on(t.detectedAt)]
);

export const overflightLog = pgTable(
  "overflight_log",
  {
    id: serial("id").primaryKey(),
    hex: varchar("hex", { length: 10 }).notNull(),
    flight: varchar("flight", { length: 10 }),
    lat: doublePrecision("lat").notNull(),
    lon: doublePrecision("lon").notNull(),
    altBaroFt: integer("alt_baro_ft"),
    distanceNm: doublePrecision("distance_nm").notNull(),
    detectedAt: timestamp("detected_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("overflight_log_detected_at_idx").on(t.detectedAt)]
);

export const homeLocation = pgTable(
  "home_location",
  {
    id: integer("id").primaryKey().default(1),
    lat: doublePrecision("lat").notNull(),
    lon: doublePrecision("lon").notNull(),
    radiusNm: doublePrecision("radius_nm").notNull().default(15),
    altitudeCeilingFt: integer("altitude_ceiling_ft").notNull().default(5000),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [check("home_location_singleton", sql`${t.id} = 1`)]
);

export const watchRegions = pgTable(
  "watch_regions",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 80 }).notNull(),
    lat: doublePrecision("lat").notNull(),
    lon: doublePrecision("lon").notNull(),
    radiusNm: doublePrecision("radius_nm").notNull().default(200),
    enabled: boolean("enabled").notNull().default(true),
    isDefault: boolean("is_default").notNull().default(false),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("watch_regions_single_default")
      .on(t.isDefault)
      .where(sql`${t.isDefault} = true`),
  ]
);

export const savedLocations = pgTable("saved_locations", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 80 }).notNull(),
  lat: doublePrecision("lat").notNull(),
  lon: doublePrecision("lon").notNull(),
  zoom: integer("zoom").notNull().default(9),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export const airports = pgTable(
  "airports",
  {
    id: serial("id").primaryKey(),
    ident: varchar("ident", { length: 10 }).notNull(),
    type: varchar("type", { length: 20 }).notNull(),
    name: varchar("name", { length: 160 }).notNull(),
    lat: doublePrecision("lat").notNull(),
    lon: doublePrecision("lon").notNull(),
    elevationFt: integer("elevation_ft"),
    icaoCode: varchar("icao_code", { length: 4 }),
    iataCode: varchar("iata_code", { length: 3 }),
    municipality: varchar("municipality", { length: 120 }),
    countryCode: varchar("country_code", { length: 2 }),
  },
  (t) => [
    uniqueIndex("airports_ident_idx").on(t.ident),
    index("airports_lat_lon_idx").on(t.lat, t.lon),
    index("airports_type_idx").on(t.type),
  ]
);

Review This

Three checks, each targeting a spot where the prompt asked for something specific and an AI assistant can plausibly deliver something that looks equivalent but isn’t.

Is every hex column actually sized for real-world data, or just for a clean ICAO address? An ICAO 24-bit address is 6 hex characters, and an AI assistant that reasons from that fact alone will size the column varchar(6), which type-checks, migrates cleanly, and works right up until the first anonymized aircraft shows up. adsb.lol prefixes anonymized or non-ICAO addresses with ~, so the real column needs headroom past 6 characters. The prompt above says “not just a clean 6-character ICAO address” for exactly this reason, but check the output anyway: look at hex: varchar("hex", { length: 10 }) in all three tables that carry it (position_snapshots, squawk_alerts, overflight_log). If any of them landed at 6, the follow-up prompt is one line: “hex columns need to be varchar(10), adsb.lol prefixes anonymized addresses with ~ and 6 characters isn’t enough room.”

Did is_default get a partial unique index, or a plain one? This is the mistake most worth understanding, because a plain uniqueIndex().on(t.isDefault) looks correct and even partially works: Postgres will happily accept one true row and one false row, then reject a second true row exactly like you wanted. It only breaks the moment you have a third watch region, because now you need a second false row too, and the plain unique index rejects that as a duplicate. The fix is a partial index, filtered with .where(sql\${t.isDefault} = true`), so only rows where is_defaultis true are indexed at all and any number of false rows can coexist. Check the schema for that.where(…)` clause specifically. If it’s missing, the follow-up prompt is: “the unique index on watch_regions.is_default needs to be partial, filtered to WHERE is_default = true, so any number of non-default regions can coexist. A plain unique index will break as soon as there are two disabled regions.”

Did home_location get an actual constraint, or just a comment saying “only insert one row”? This is the easiest one for an AI assistant to skip entirely, because a single-row table is a strange enough requirement that “trust the app code” feels like the natural default. Check for check("home_location_singleton", sql\${t.id} = 1`)` in the table definition. If it’s missing, or if the AI instead reached for something like a fixed UUID default with no enforcement, the follow-up prompt is: “home_location needs a database-level constraint that guarantees at most one row, not an application convention. Use a check constraint tying the primary key to a fixed value.”

None of these three would show up in npm run typecheck. All three are Postgres-level guarantees, not TypeScript-level ones, which means the only way to catch a missing one is to read the schema by eye or exercise the constraint directly against a real database. That’s what the next section does.

Try It

1. Run the prompt above against your AI coding assistant of choice, with apps/server already scaffolded from the previous lesson.
2. Read the generated schema.ts against the three checks above before running anything.
3. Run npx drizzle-kit generate and read the generated SQL. Confirm it includes a CREATE UNIQUE INDEX ... WHERE is_default = true statement, not a plain CREATE UNIQUE INDEX, and confirm it includes a CHECK constraint on home_location.
4. Run npm run db:migrate and confirm with psql $DATABASE_URL -c "\dt" that all seven tables exist.
5. In psql, insert two watch regions and set is_default = true on both. The second should fail with a 23505 unique violation. Set the first back to false and confirm the second now succeeds, then confirm a third region with is_default = false inserts without conflict.
6. In psql, insert a second row into home_location with id = 2. Confirm it fails with a 23514 check violation.

Recap

• An AI assistant models columns readily but skips invariants unless you name them explicitly. State the constraint, not just the shape.
• A plain unique index and a partial unique index look almost identical in generated code and behave identically until the specific case (a second false row) the partial index exists to allow. Reading the index definition, not just its name, is the only way to tell them apart.
• A check constraint is the same idea for a single-row table: cheap to state in a prompt, easy for an AI assistant to skip, and the only thing standing between “the app always uses id = 1” as a convention and as a guarantee.
• None of these three checks show up in a type error. They only show up if you read the schema or exercise the constraint against a real database, which is why Try It ends with breaking both invariants on purpose.

Next lesson: prompting the Fastify server and the first full CRUD resource, watch regions, into existence.