Database Schema · SKYWATCH: Build a Real-Time Flight Tracker

Objectives

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

Explain why Drizzle over a heavier ORM like Prisma for this app
Model the core tables: position_snapshots, squawk_alerts, overflight_log, home_location, watch_regions, saved_locations, airports
Use the generate → review → migrate workflow you’ll reuse every time the schema changes for the rest of the course

💡 Why this matters: Every table you model here — and the invariants you push into the database instead of the app — is something later modules build directly on top of, so getting it right now saves a painful migration later.

Why Drizzle

Drizzle’s schema is plain TypeScript functions that produce both the SQL DDL and the inferred TypeScript row types — no separate schema language, no code-generation step you have to remember to re-run before your editor’s types are accurate. Compared to something like Prisma, that’s a smaller mental model: pgTable(...) is a function call you can read top to bottom, and typeof watchRegions.$inferSelect gives you the exact row shape with zero extra tooling.

The tradeoff is that Drizzle is a thinner layer — you write more raw-shaped queries (db.select().from(x).where(eq(...))) instead of a rich query builder with relations resolved automatically. For an app this size, with a handful of tables and no deep relational joins, that’s the right tradeoff: less abstraction to fight when a query needs to do something slightly unusual (which, as you’ll see in Module 2, it eventually does — a transactional “clear every other row’s flag, then set this one” operation that’s easier to reason about in Drizzle’s thin style than through a heavier ORM’s relation graph).

Install and Configure

bash

cd apps/server
npm install drizzle-orm pg dotenv
npm install -D drizzle-kit @types/pg

// 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,
});

schema points at one file that will grow to hold every table (this course keeps them all in schema.ts rather than splitting per-table — for a schema this size, one file you can Ctrl-F beats jumping between a dozen files). out is where drizzle-kit generate writes migration SQL — never hand-edit files in there except the one time in Module 15 where you’ll add a backfill statement to a freshly generated migration.

The Connection

// 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 });

Hang onto pool as a separate export, not just db — Module 2’s realtime layer needs a second, dedicated, never-pooled connection for Postgres LISTEN, and the reasoning for why the pooled pool here can’t be reused for that is worth understanding once you get there.

The Schema — Seven Tables to Start

// 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";

/**
 * One row per aircraft per poll cycle. Backs trails (recent rows for a hex)
 * and historical playback (rows in a time range). Written continuously by
 * the background poller.
 */
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),
  ]
);

/** Emergency squawk (7500/7600/7700) sightings, one row per detection. */
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)]
);

/** "Who flew over my house" log: aircraft within radiusNm and below altitudeCeilingFt of home. */
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)]
);

/**
 * Single-row table holding the "home" reference point used by the
 * overflight tracker. Enforced to exactly one row via a check constraint
 * rather than application logic -- the database, not the app, is the
 * source of truth for this invariant.
 */
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`)]
);

/**
 * Region(s) the background poller continuously watches. Exactly one region
 * may be the operator's default (isDefault) -- the region the map opens on
 * and the "return to watch region" control jumps back to. Enforced as a
 * *partial* unique index, on isDefault, filtered to WHERE isDefault = true:
 * an ordinary unique index on a boolean column would only ever allow one
 * `true` row and one `false` row total, which isn't what we want (we want
 * "at most one true row", with any number of false rows). Postgres partial
 * indexes solve exactly this by indexing only the rows matching the WHERE
 * clause -- every `false` row is simply invisible to this index, so any
 * number of them can coexist, while a second `true` row collides with the
 * existing one and gets rejected with a real constraint violation. Same
 * category of decision as home_location's check constraint below: the
 * invariant lives in the database, not in application code someone has to
 * remember to enforce correctly on every write path.
 */
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`),
  ]
);

/**
 * User-saved map locations for a "jump to a favorite spot" convenience --
 * distinct from watch_regions above: saving one here never tells the
 * poller to start watching anything, it's purely a frontend map-view
 * bookmark. A later module builds the UI for both, and is explicit about
 * keeping the two features separate.
 */
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(),
});

/** Airport reference data (OurAirports open dataset), for the map overlay. */
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),
  ]
);

A few things worth noticing before moving on:

Every “hex” column is varchar(10), not the 6 characters an ICAO 24-bit address actually needs. adsb.lol prefixes anonymized/non-ICAO addresses with ~, so the real-world column needs headroom. (In the actual SKYWATCH build, this was originally varchar(6) and had to be widened in a later migration once anonymized addresses started overflowing it in production — sized correctly here so you don’t have to repeat that fix.)
check("home_location_singleton", sql\${t.id} = 1`)— this is a database-level invariant, not an app-level one. The application code *could* just always query/updateWHERE id = 1and never insert a second row, but that trusts every future line of code that ever touches this table to remember the rule. The constraint makes violating it a23514` Postgres error instead of a silent data-integrity bug three months from now.
watch_regions.enabled (a boolean, not deleting the row) is what lets the watch-regions UI toggle a region on/off without losing its configuration — deletion and disabling are different operations with different UI affordances, and conflating them is a common modeling mistake.
isDefault is a plain boolean column, but “at most one region can be default” is a constraint no single boolean type can express on its own — that’s what the partial unique index above is for. The next module builds the repo layer that keeps this invariant honest on every write (clearing the old default before setting a new one, inside a transaction), but the database-level guarantee here means even a bug in that application code can’t produce two default regions; Postgres simply rejects the second one.
saved_locations and watch_regions look similar (both are named lat/lon/radius-ish rows a user creates) but back two genuinely different features — a later module builds the UI for both side by side and is explicit about why saving a location here never affects what the poller watches.
airports gets its own airports_ident_idx unique index because a later module’s seed script needs an idempotent “insert or skip” against ident (an airport’s ICAO/local identifier) — re-running the seed against a database that already has data shouldn’t produce duplicate rows.

Generate and Run Your First Migration

bash

cd apps/server
npx drizzle-kit generate

This diffs your schema.ts against nothing (first run) and writes a numbered .sql file into apps/server/drizzle/, plus a JSON snapshot in drizzle/meta/ that the next generate diffs against. Always read the generated SQL before running it — drizzle-kit generate is very good, but it’s diffing structural TypeScript against structural SQL, and the two don’t always have a single obviously-correct translation (a renamed column, for instance, generates a drop-then-add by default unless you tell it otherwise interactively).

// apps/server/src/db/migrate.ts
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { db, pool } from "./client.js";

async function main() {
  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle" });
  console.log("Migrations complete.");
  await pool.end();
}

main().catch((err) => {
  console.error("Migration failed:", err);
  process.exit(1);
});

Add "db:generate": "drizzle-kit generate" and "db:migrate": "tsx src/db/migrate.ts" to apps/server/package.json’s scripts, then, with Postgres running and DATABASE_URL set in apps/server/.env:

bash

npm run db:migrate

You’ll repeat exactly this two-step dance — db:generate, read the SQL, db:migrate — every time a later module adds or changes a table.

Try It

Run npx drizzle-kit generate and read the generated SQL before running it. Confirm it includes both the watch_regions table and the partial CREATE UNIQUE INDEX ... WHERE is_default = true statement — a partial index’s WHERE clause is easy to lose sight of when skimming generated SQL, so check it’s actually there.
Run npm run db:migrate and confirm with psql $DATABASE_URL -c "\dt" that all seven tables exist.
In psql, insert two watch regions and try setting is_default = true on both. The second one should fail with a 23505 unique violation — that’s the partial index doing its job. Set the first back to is_default = false and confirm the second now succeeds.
Confirm npm run typecheck (from the repo root) still passes.

Recap

Drizzle’s schema is plain TypeScript, with the generated SQL migration as the reviewable, checked-in artifact of any schema change.
A check constraint enforces a single-row table; a partial uniqueIndex (indexed rows filtered by a WHERE clause) enforces “at most one row where some boolean is true.” Both push an invariant into the database instead of trusting every future write path to honor it.
Every hex column is sized varchar(10), not the 6 characters an ICAO address needs, because adsb.lol prefixes anonymized addresses with ~ — a real production lesson from the actual SKYWATCH build, where an original varchar(6) column had to be widened later.
The generate → read → migrate workflow is the only schema-change process this course uses, from here through every later module.

Next lesson: standing up the Fastify server and building the first full CRUD resource, watch regions, end to end.