Challenge 3: Write an Up/Down Migration — Possible Solution
====================================================================

-- Migration: 2026070401_add_discontinued_to_products.sql

-- UP: apply the change
ALTER TABLE products
  ADD COLUMN discontinued BOOLEAN NOT NULL DEFAULT FALSE;

CREATE INDEX idx_products_discontinued ON products (discontinued);

-- DOWN: revert the change
DROP INDEX IF EXISTS idx_products_discontinued;

ALTER TABLE products
  DROP COLUMN discontinued;


Equivalent Prisma-style migration (schema.prisma diff + generated SQL):

// schema.prisma (before)
model Product {
  id    Int    @id @default(autoincrement())
  name  String
  price Float
}

// schema.prisma (after)
model Product {
  id            Int     @id @default(autoincrement())
  name          String
  price         Float
  discontinued  Boolean @default(false)

  @@index([discontinued])
}

// `prisma migrate dev --name add_discontinued_to_products` diffs the two
// schema versions and generates the up/down SQL automatically — the
// hand-written SQL above is what that command would produce.


TypeScript type regenerated after the migration runs:

interface Product {
  id: number;
  name: string;
  price: number;
  discontinued: boolean; // new field, now required everywhere Product is used
}

// Any code constructing a Product literal without `discontinued` now fails
// to compile — the type system forces every call site to be updated
// consistently with the schema change, instead of quietly returning
// `undefined` for a column that used to not exist.

WHY THIS WORKS
--------------
- The UP migration is idempotent in intent (one clear forward change) and
  the DOWN migration exactly reverses it — dropping the index before the
  column, since dropping the column first would make the index-drop
  redundant but the coupled ordering matters for some databases/tooling.
- DEFAULT FALSE means every existing row gets a valid value immediately;
  the migration never leaves existing rows with NULL in a NOT NULL column.
- Once the schema changes, regenerating types (`prisma generate` or
  equivalent) means the TypeScript Product interface picks up the new field
  automatically — any code that builds a Product object without it becomes
  a compile error, catching every place that needs updating.