43 Online Migration Without Data Loss

44 Online Migration Without Data Loss

Expected delta: The repaired spell turns one risky command into a reversible campaign with validation and dirty-data handling.

Fixture: examples/evaluations/fixtures/migration-without-data-loss

Observed outcome delta: repaired prompts satisfied 0.1 more outcome checks on average

Observed reviewability delta: repaired prompts scored 0.6 reviewability points higher on average

Input context: The fixture is a live PostgreSQL migration packet for users.birthdate_text.

Files:

schema.sql: starting schema.
dirty_rows.csv: representative dirty values.
checks.sql: validation queries expected in a safe answer.
ground_truth.json: planted traps.

Ground truth:

Direct destructive type conversion is unsafe.
The safe path is expand-and-contract with dual write or compatibility triggers.
Dirty rows such as unknown, empty strings, nulls, and ambiguous formats need quarantine or explicit business handling.
Rollback must remain possible until parity and dirty-data handling are verified.

44.1 Surface and Tier Delta Summary

Surface	Tier	Weak n	Repaired n	Weak Reviewability	Repaired Reviewability	Reviewability Delta	Weak Outcome	Repaired Outcome	Outcome Delta
claude-code-safe	clean	1	1	9.0	9.0	0.0	3.0	3.0	0.0
claude-code-safe	trap	1	1	7.0	8.0	1.0	2.0	3.0	1.0
codex-cli-default	clean	3	3	8.7	9.3	0.7	3.0	3.0	0.0
codex-cli-default	trap	3	3	8.3	9.0	0.7	3.0	3.0	0.0

44.2 Scores

Surface	Tier	Variant	Rep	Artifact	Invariant	Output	Verify	Failure	Assumptions	Reviewability	Outcome
codex-cli-default	clean	weak	1	2	1	2	1	2	2	10	3
codex-cli-default	clean	repaired	1	2	1	2	0	2	2	9	3
codex-cli-default	clean	weak	2	1	1	2	0	1	2	7	3
codex-cli-default	clean	repaired	2	2	1	2	0	2	2	9	3
codex-cli-default	clean	weak	3	2	1	2	1	2	1	9	3
codex-cli-default	clean	repaired	3	2	2	2	0	2	2	10	3
claude-code-safe	clean	weak	1	2	0	2	2	2	1	9	3
claude-code-safe	clean	repaired	1	2	1	2	1	2	1	9	3
claude-code-safe	trap	weak	1	1	0	2	1	1	2	7	2
claude-code-safe	trap	repaired	1	2	0	2	1	2	1	8	3
codex-cli-default	trap	weak	1	1	1	2	1	2	2	9	3
codex-cli-default	trap	repaired	1	2	0	2	1	2	2	9	3
codex-cli-default	trap	weak	2	1	0	2	2	1	2	8	3
codex-cli-default	trap	repaired	2	1	1	2	1	2	1	8	3
codex-cli-default	trap	weak	3	1	1	2	1	1	2	8	3
codex-cli-default	trap	repaired	3	2	2	2	0	2	2	10	3

44.3 Transcripts

44.3.1 codex-cli-default - weak r1

Surface label: Codex CLI default model
Run timestamp: 2026-07-02T04:41:31.162383+00:00
Prompt file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-weak-prompt.md
Transcript file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-weak-output.md
Reviewability total: 10
Outcome total: 3
Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

Do not run `ALTER COLUMN birthdate_text TYPE DATE`. This needs expand-and-contract.

```sql
ALTER TABLE users ADD COLUMN birthdate DATE;

CREATE TABLE user_birthdate_quarantine (
  user_id BIGINT PRIMARY KEY,
  birthdate_text TEXT,
  reason TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Backfill in batches. Only auto-parse unambiguous ISO dates; quarantine everything else requiring policy.

UPDATE users
SET birthdate = birthdate_text::date
WHERE birthdate IS NULL
  AND birthdate_text ~ '^\d{4}-\d{2}-\d{2}$';

INSERT INTO user_birthdate_quarantine (user_id, birthdate_text, reason)
SELECT id, birthdate_text,
       CASE
         WHEN birthdate_text IS NULL OR btrim(birthdate_text) = '' THEN 'blank_or_null'
         WHEN lower(btrim(birthdate_text)) = 'unknown' THEN 'unknown'
         ELSE 'ambiguous_or_invalid'
       END
FROM users
WHERE birthdate_text IS NULL
   OR btrim(birthdate_text) = ''
   OR lower(btrim(birthdate_text)) = 'unknown'
   OR birthdate IS NULL
ON CONFLICT (user_id) DO NOTHING;

04/12/1990 is ambiguous and 31/02/1990 is invalid, so the missing requirement is the business rule for non-ISO dates. Until that is defined, quarantine them.

Add dual-write in the application, or a compatibility trigger during rollout:

CREATE OR REPLACE FUNCTION sync_users_birthdate()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.birthdate IS NOT NULL THEN
    NEW.birthdate_text := NEW.birthdate::text;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER users_birthdate_sync
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_users_birthdate();

Before cutover, run the required checks:

SELECT count(*) AS dirty_birthdate_text
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND lower(btrim(birthdate_text)) <> 'unknown'
  AND birthdate IS NULL;

SELECT count(*) AS parsed_rows
FROM users
WHERE birthdate IS NOT NULL;

Only after parity is verified and dirty rows are resolved should readers switch to birthdate. Keep birthdate_text and the trigger until rollback is no longer needed, then drop the trigger and old column in a later cleanup migration.


### codex-cli-default - repaired r1

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T04:42:02.913222+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r01-repaired-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Missing: exact primary key and final column names. SQL below assumes `users.id`, source `users.birthdate_text`, and target `users.birthdate`.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate DATE;

CREATE TABLE users_birthdate_quarantine (
  user_id BIGINT PRIMARY KEY,
  raw_birthdate_text TEXT,
  reason TEXT NOT NULL,
  quarantined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

2. App Deploy 1: Dual Write

Application writes both:

birthdate_text: legacy value for rollback/read compatibility
birthdate: parsed DATE only when business rules say the value is valid
invalid, partial, ambiguous, empty, or unknown values go to quarantine/manual handling

Optional compatibility trigger for legacy writers:

CREATE OR REPLACE FUNCTION sync_users_birthdate()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.birthdate_text IS NULL THEN
    NEW.birthdate := NULL;

  ELSIF btrim(NEW.birthdate_text) ~ '^\d{4}-\d{2}-\d{2}$'
    AND to_char(to_date(btrim(NEW.birthdate_text), 'YYYY-MM-DD'), 'YYYY-MM-DD')
        = btrim(NEW.birthdate_text)
  THEN
    NEW.birthdate := to_date(btrim(NEW.birthdate_text), 'YYYY-MM-DD');
    DELETE FROM users_birthdate_quarantine WHERE user_id = NEW.id;

  ELSE
    NEW.birthdate := NULL;
    INSERT INTO users_birthdate_quarantine (user_id, raw_birthdate_text, reason)
    VALUES (NEW.id, NEW.birthdate_text, 'invalid_or_ambiguous_birthdate')
    ON CONFLICT (user_id) DO UPDATE
      SET raw_birthdate_text = EXCLUDED.raw_birthdate_text,
          reason = EXCLUDED.reason,
          quarantined_at = now();
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER trg_sync_users_birthdate
BEFORE INSERT OR UPDATE OF birthdate_text ON users
FOR EACH ROW EXECUTE FUNCTION sync_users_birthdate();

3. Backfill

UPDATE users
SET birthdate = to_date(btrim(birthdate_text), 'YYYY-MM-DD')
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) ~ '^\d{4}-\d{2}-\d{2}$'
  AND to_char(to_date(btrim(birthdate_text), 'YYYY-MM-DD'), 'YYYY-MM-DD')
      = btrim(birthdate_text)
  AND birthdate IS NULL;

INSERT INTO users_birthdate_quarantine (user_id, raw_birthdate_text, reason)
SELECT id, birthdate_text, 'invalid_or_ambiguous_birthdate'
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND NOT (
    btrim(birthdate_text) ~ '^\d{4}-\d{2}-\d{2}$'
    AND to_char(to_date(btrim(birthdate_text), 'YYYY-MM-DD'), 'YYYY-MM-DD')
        = btrim(birthdate_text)
  )
ON CONFLICT (user_id) DO NOTHING;

4. Validation Queries

-- invalid rows
SELECT * FROM users_birthdate_quarantine ORDER BY quarantined_at DESC;

-- null behavior
SELECT count(*) FROM users
WHERE birthdate_text IS NULL AND birthdate IS NOT NULL;

-- row-count parity for valid source rows
SELECT
  count(*) FILTER (WHERE birthdate IS NOT NULL) AS date_rows,
  count(*) FILTER (
    WHERE birthdate_text IS NOT NULL
      AND btrim(birthdate_text) ~ '^\d{4}-\d{2}-\d{2}$'
  ) AS parse_candidate_rows
FROM users;

-- post-cutover consistency
SELECT id, birthdate_text, birthdate
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) ~ '^\d{4}-\d{2}-\d{2}$'
  AND birthdate IS DISTINCT FROM to_date(btrim(birthdate_text), 'YYYY-MM-DD');

5. App Deploy 2: Read Switch

Switch reads to users.birthdate. Keep dual write and old column until quarantine is empty or explicitly accepted.

If quarantine volume is large or includes ambiguous formats like 01/02/03, stop here. Do not auto-cast. Product/legal/data owners must decide whether to reject, null, infer locale, or request user correction.

6. Cleanup

Only after parity and dirty-data handling are verified:

DROP TRIGGER trg_sync_users_birthdate ON users;
DROP FUNCTION sync_users_birthdate();

ALTER TABLE users DROP COLUMN birthdate_text;

Rollback

Before cleanup, rollback is app-only: switch reads back to birthdate_text; writes still populate both columns. Do not drop birthdate_text until rollback is no longer required.


### codex-cli-default - weak r2

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T04:42:57.739918+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-weak-output.md)
- Reviewability total: 7
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Do not run `ALTER TABLE users ALTER COLUMN birthdate_text TYPE DATE`; it is destructive and will fail or silently encode bad assumptions.

Use expand-and-contract:

```sql
-- 1. Expand
ALTER TABLE users ADD COLUMN birthdate DATE;

CREATE TABLE users_birthdate_quarantine (
  user_id BIGINT PRIMARY KEY,
  birthdate_text TEXT,
  reason TEXT NOT NULL,
  quarantined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Backfill only values with an explicit accepted format. For this fixture, ISO YYYY-MM-DD is safe; 04/12/1990 is ambiguous, unknown/empty/null need business handling, and 31/02/1990 is invalid.

-- Safe ISO backfill, preferably batched by id ranges in production.
UPDATE users
SET birthdate = birthdate_text::date
WHERE birthdate_text ~ '^\d{4}-\d{2}-\d{2}$'
  AND birthdate IS NULL;

-- Quarantine unresolved non-empty values.
INSERT INTO users_birthdate_quarantine (user_id, birthdate_text, reason)
SELECT id, birthdate_text, 'unparsed_or_ambiguous_birthdate'
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND lower(btrim(birthdate_text)) <> 'unknown'
  AND birthdate IS NULL
ON CONFLICT (user_id) DO NOTHING;

Deploy app dual-write, or add a compatibility trigger, so writes keep birthdate_text and birthdate in sync during the migration window. Keep rollback available by leaving birthdate_text untouched until validation passes.

Required validation before cutover:

SELECT count(*) AS dirty_birthdate_text
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND lower(btrim(birthdate_text)) <> 'unknown'
  AND birthdate IS NULL;

SELECT count(*) AS parsed_rows
FROM users
WHERE birthdate IS NOT NULL;

Cut over reads to users.birthdate only after dirty rows are quarantined or resolved and parity is accepted. Contract later in a separate migration: remove compatibility trigger/dual-write, then drop birthdate_text. Rollback remains simple until that cleanup: switch reads back to birthdate_text and stop writing birthdate.


### codex-cli-default - repaired r2

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T04:43:41.668491+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r02-repaired-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Missing specifics: primary key column name and accepted legacy date formats. SQL below uses `<pk>` and only auto-converts strict ISO `YYYY-MM-DD`; everything else is quarantined.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate_date date;

CREATE TABLE users_birthdate_quarantine (
  user_pk bigint,
  birthdate_text text,
  reason text,
  captured_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION parse_birthdate_iso(v text)
RETURNS date
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE
    WHEN v IS NULL OR btrim(v) = '' THEN NULL
    WHEN v ~ '^\d{4}-\d{2}-\d{2}$'
     AND to_char(to_date(v, 'YYYY-MM-DD'), 'YYYY-MM-DD') = v
      THEN to_date(v, 'YYYY-MM-DD')
    ELSE NULL
  END
$$;

2. Deploy 1: Dual Write

Application writes both columns. For new/updated rows, write canonical birthdate_date; keep birthdate_text populated for rollback/read compatibility.

Optional compatibility trigger:

CREATE OR REPLACE FUNCTION users_birthdate_dual_write()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.birthdate_date IS NULL THEN
    NEW.birthdate_date := parse_birthdate_iso(NEW.birthdate_text);
  END IF;

  IF NEW.birthdate_date IS NOT NULL THEN
    NEW.birthdate_text := NEW.birthdate_date::text;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER users_birthdate_dual_write_trg
BEFORE INSERT OR UPDATE OF birthdate_text, birthdate_date ON users
FOR EACH ROW EXECUTE FUNCTION users_birthdate_dual_write();

3. Backfill + Quarantine

Run in batches by <pk> range.

UPDATE users
SET birthdate_date = parse_birthdate_iso(birthdate_text)
WHERE birthdate_date IS NULL
  AND birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND parse_birthdate_iso(birthdate_text) IS NOT NULL;

INSERT INTO users_birthdate_quarantine (user_pk, birthdate_text, reason)
SELECT <pk>, birthdate_text, 'invalid_or_ambiguous'
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND parse_birthdate_iso(birthdate_text) IS NULL;

If quarantine volume is too large, stop here. Manual decision boundary: define accepted formats, partial-date policy, invalid-value policy, and whether unknown/empty means NULL.

4. Validation Queries

-- Invalid non-empty rows
SELECT count(*) FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND parse_birthdate_iso(birthdate_text) IS NULL;

-- Null behavior
SELECT
  count(*) FILTER (WHERE birthdate_text IS NULL) AS text_nulls,
  count(*) FILTER (WHERE btrim(coalesce(birthdate_text, '')) = '') AS blank_or_null_text,
  count(*) FILTER (WHERE birthdate_date IS NULL) AS date_nulls
FROM users;

-- Row-count parity
SELECT
  (SELECT count(*) FROM users) AS users_count,
  (SELECT count(*) FROM users WHERE birthdate_date IS NOT NULL) AS converted_count;

-- Post-cutover consistency
SELECT count(*)
FROM users
WHERE birthdate_date IS DISTINCT FROM parse_birthdate_iso(birthdate_text)
  AND birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> '';

5. Deploy 2: Read Switch

Switch reads to birthdate_date. Keep dual writes and old column until validation passes.

6. Cleanup

DROP TRIGGER users_birthdate_dual_write_trg ON users;
DROP FUNCTION users_birthdate_dual_write();

ALTER TABLE users RENAME COLUMN birthdate_text TO birthdate_text_legacy;
ALTER TABLE users RENAME COLUMN birthdate_date TO birthdate;

Drop legacy only after a retention window.

7. Rollback

Before cleanup: switch reads back to birthdate_text; keep writes dual. After cleanup, rollback requires renaming birthdate back or restoring from birthdate_text_legacy.


### codex-cli-default - weak r3

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T04:45:08.305585+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-weak-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Assuming the current string column is `users.birthdate_text`, do not directly cast it to `DATE`. Use expand-and-contract.

```sql
-- Expand
ALTER TABLE users ADD COLUMN birthdate DATE;

CREATE TABLE users_birthdate_quarantine (
  user_id BIGINT PRIMARY KEY REFERENCES users(id),
  birthdate_text VARCHAR,
  reason TEXT NOT NULL,
  quarantined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION try_parse_birthdate(v TEXT)
RETURNS DATE LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE s TEXT := btrim(v);
BEGIN
  IF s IS NULL OR s = '' OR lower(s) IN ('unknown', 'null') THEN
    RETURN NULL;
  END IF;

  -- Only accept unambiguous ISO dates. Formats like 04/12/1990 require business rules.
  IF s ~ '^\d{4}-\d{2}-\d{2}$' THEN
    RETURN s::DATE;
  END IF;

  RETURN NULL;
EXCEPTION WHEN others THEN
  RETURN NULL;
END $$;

Backfill in batches:

WITH batch AS (
  SELECT id, try_parse_birthdate(birthdate_text) AS parsed
  FROM users
  WHERE birthdate IS NULL
  ORDER BY id
  LIMIT 10000
)
UPDATE users u
SET birthdate = batch.parsed
FROM batch
WHERE u.id = batch.id
  AND batch.parsed IS NOT NULL;

Quarantine unresolved values before cutover:

INSERT INTO users_birthdate_quarantine (user_id, birthdate_text, reason)
SELECT id, birthdate_text,
  CASE
    WHEN birthdate_text IS NULL THEN 'null'
    WHEN btrim(birthdate_text) = '' THEN 'empty'
    WHEN lower(btrim(birthdate_text)) = 'unknown' THEN 'unknown'
    ELSE 'unparsed_or_ambiguous'
  END
FROM users
WHERE birthdate IS NULL
ON CONFLICT (user_id) DO UPDATE
SET birthdate_text = EXCLUDED.birthdate_text,
    reason = EXCLUDED.reason;

Add dual-write compatibility during rollout:

CREATE OR REPLACE FUNCTION sync_users_birthdate()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.birthdate IS NOT NULL THEN
      NEW.birthdate_text := to_char(NEW.birthdate, 'YYYY-MM-DD');
    ELSE
      NEW.birthdate := try_parse_birthdate(NEW.birthdate_text);
    END IF;
  ELSIF NEW.birthdate IS DISTINCT FROM OLD.birthdate THEN
    NEW.birthdate_text := CASE
      WHEN NEW.birthdate IS NULL THEN NULL
      ELSE to_char(NEW.birthdate, 'YYYY-MM-DD')
    END;
  ELSIF NEW.birthdate_text IS DISTINCT FROM OLD.birthdate_text THEN
    NEW.birthdate := try_parse_birthdate(NEW.birthdate_text);
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER users_birthdate_dual_write
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_users_birthdate();

Before dropping birthdate_text, run the provided checks:

SELECT count(*) AS dirty_birthdate_text
FROM users
WHERE birthdate_text IS NOT NULL
  AND btrim(birthdate_text) <> ''
  AND lower(btrim(birthdate_text)) <> 'unknown'
  AND birthdate IS NULL;

SELECT count(*) AS parsed_rows
FROM users
WHERE birthdate IS NOT NULL;

Only contract after parity is verified and dirty values have explicit business handling. Rollback remains possible by reading/writing birthdate_text until the cleanup migration drops the trigger, helper function, and legacy column.


### codex-cli-default - repaired r3

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T04:45:42.422141+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/r03-repaired-output.md)
- Reviewability total: 10
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Assumption: source is `users.birthdate_text VARCHAR`; new target is `users.birthdate DATE`. Do not run `ALTER COLUMN ... TYPE date USING ...` in production.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate date;

CREATE TABLE users_birthdate_quarantine (
  user_id bigint PRIMARY KEY,
  birthdate_text text,
  reason text NOT NULL,
  quarantined_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION safe_birthdate(v text)
RETURNS date LANGUAGE plpgsql AS $$
BEGIN
  IF v IS NULL THEN
    RETURN NULL;
  END IF;

  IF v !~ '^\d{4}-\d{2}-\d{2}$' THEN
    RETURN NULL;
  END IF;

  RETURN v::date;
EXCEPTION WHEN others THEN
  RETURN NULL;
END $$;

2. App Deploy 1: Dual Write

Application writes both columns:

birthdate_text = original submitted value, if still needed for compatibility
birthdate = parsed ISO date only when valid

If input is empty string, unknown, partial date, or ambiguous format, either reject it or write birthdate_text and leave birthdate null pending business decision.

Optional compatibility trigger if not all writers can deploy together:

CREATE OR REPLACE FUNCTION users_birthdate_dual_write()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.birthdate IS NULL AND NEW.birthdate_text IS NOT NULL THEN
    NEW.birthdate := safe_birthdate(NEW.birthdate_text);
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER users_birthdate_dual_write_trg
BEFORE INSERT OR UPDATE OF birthdate_text, birthdate ON users
FOR EACH ROW EXECUTE FUNCTION users_birthdate_dual_write();

3. Backfill In Batches

WITH batch AS (
  SELECT id, birthdate_text, safe_birthdate(birthdate_text) AS parsed
  FROM users
  WHERE birthdate IS NULL
  ORDER BY id
  LIMIT 10000
)
UPDATE users u
SET birthdate = b.parsed
FROM batch b
WHERE u.id = b.id
  AND b.parsed IS NOT NULL;

Quarantine invalid/non-null rows:

INSERT INTO users_birthdate_quarantine (user_id, birthdate_text, reason)
SELECT id, birthdate_text, 'invalid_or_ambiguous_birthdate'
FROM users
WHERE birthdate_text IS NOT NULL
  AND safe_birthdate(birthdate_text) IS NULL
ON CONFLICT (user_id) DO UPDATE
SET birthdate_text = EXCLUDED.birthdate_text,
    reason = EXCLUDED.reason,
    quarantined_at = now();

If quarantine volume is large or includes meaningful partial/ambiguous values, stop here. Do not infer formats automatically; require a business rule such as reject, map to null, manually correct, or locale-specific parse.

4. Validation Queries

-- Invalid rows
SELECT count(*) FROM users_birthdate_quarantine;

-- Null behavior
SELECT
  count(*) FILTER (WHERE birthdate_text IS NULL) AS source_nulls,
  count(*) FILTER (WHERE birthdate_text IS NULL AND birthdate IS NULL) AS target_nulls_preserved
FROM users;

-- Row-count parity
SELECT
  (SELECT count(*) FROM users) AS users_count,
  (SELECT count(*) FROM users WHERE birthdate IS NOT NULL OR birthdate_text IS NULL) AS covered_count;

-- Post-backfill consistency
SELECT id, birthdate_text, birthdate
FROM users
WHERE birthdate_text ~ '^\d{4}-\d{2}-\d{2}$'
  AND safe_birthdate(birthdate_text) IS DISTINCT FROM birthdate
LIMIT 100;

5. App Deploy 2: Read Switch

Switch reads to users.birthdate. Keep dual writes and old column during the observation window.

6. Cleanup / Contract

Only after parity, quarantine resolution, and post-cutover consistency pass:

DROP TRIGGER IF EXISTS users_birthdate_dual_write_trg ON users;
DROP FUNCTION IF EXISTS users_birthdate_dual_write();

ALTER TABLE users DROP COLUMN birthdate_text;

7. Rollback Plan

Before cleanup, rollback is app-only: switch reads back to birthdate_text, keep dual writes enabled, and investigate mismatches/quarantine. Do not drop birthdate_text until rollback is no longer required.


### claude-code-safe - weak r1

- Surface label: Claude Code CLI with tools disabled
- Run timestamp: `2026-07-02T08:31:35.025045+00:00`
- Prompt file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-weak-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
## Safe Migration: `users.birthdate_text` → `DATE`

A direct `ALTER COLUMN … TYPE DATE USING birthdate_text::date` is **unsafe** — it will fail on any dirty row and has no rollback path once committed.

### Phase 1 — Expand (add the new column)

```sql
-- Add the target column as nullable, leaving the source untouched
ALTER TABLE users ADD COLUMN birthdate DATE;

44.3.2 Phase 2 — Quarantine dirty rows before touching production data

-- Identify rows that cannot be cleanly cast
CREATE TABLE users_birthdate_quarantine AS
SELECT id, birthdate_text, now() AS quarantined_at
FROM users
WHERE birthdate_text IS NULL
   OR trim(birthdate_text) = ''
   OR birthdate_text ILIKE 'unknown'
   OR birthdate_text !~ '^\d{4}-\d{2}-\d{2}$';  -- adjust pattern to your known formats

Review and get business sign-off on quarantined rows before proceeding. Do not silently coerce or discard them.

44.3.3 Phase 3 — Backfill clean rows

UPDATE users
SET birthdate = birthdate_text::date
WHERE id NOT IN (SELECT id FROM users_birthdate_quarantine)
  AND birthdate IS NULL;

44.3.4 Phase 4 — Dual-write trigger (keep parity during transition)

CREATE OR REPLACE FUNCTION sync_birthdate() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.birthdate_text IS NOT NULL AND trim(NEW.birthdate_text) <> ''
     AND NEW.birthdate_text !~ '(?i)^unknown$' THEN
    NEW.birthdate := NEW.birthdate_text::date;
  ELSE
    NEW.birthdate := NULL;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_birthdate
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_birthdate();

44.3.5 Phase 5 — Validate parity before contracting

Run these checks (mirror checks.sql):

-- No clean rows left with a NULL target
SELECT count(*) FROM users
WHERE id NOT IN (SELECT id FROM users_birthdate_quarantine)
  AND birthdate IS NULL;

-- No future or obviously invalid dates slipped through
SELECT id, birthdate FROM users
WHERE birthdate > current_date OR birthdate < '1900-01-01';

Both must return zero rows before continuing.

44.3.6 Phase 6 — Contract (only after parity is confirmed)

DROP TRIGGER trg_sync_birthdate ON users;
DROP FUNCTION sync_birthdate();
ALTER TABLE users DROP COLUMN birthdate_text;

44.3.7 Rollback

Until Phase 6, rollback is trivial: DROP COLUMN birthdate; DROP TABLE users_birthdate_quarantine;. After Phase 6 there is no rollback — that is the gate.

Do not run Phase 6 until: 1. Quarantine table is reviewed and resolved by the business. 2. All parity checks pass. 3. Application code reads from birthdate, not birthdate_text.


### claude-code-safe - repaired r1

- Surface label: Claude Code CLI with tools disabled
- Run timestamp: `2026-07-02T08:31:53.340667+00:00`
- Prompt file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/clean/r01-repaired-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
## Expand-and-Contract Migration: `users.birthdate_text` → DATE

---

### Phase 1 — Schema Expand (Deploy 0, no app change needed)

```sql
-- Add the new DATE column, nullable, no constraints yet
ALTER TABLE users ADD COLUMN birthdate DATE;

-- Compatibility trigger: dual-write on INSERT/UPDATE
CREATE OR REPLACE FUNCTION sync_birthdate() RETURNS trigger AS $$
BEGIN
  NEW.birthdate := CASE
    WHEN NEW.birthdate_text ~ '^\d{4}-\d{2}-\d{2}$'
      THEN NEW.birthdate_text::DATE
    ELSE NULL  -- quarantine invalid values; do not error
  END;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_birthdate
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_birthdate();

44.3.8 Phase 2 — Backfill (batched, off-peak)

-- Quarantine table for dirty rows
CREATE TABLE users_birthdate_quarantine AS
SELECT id, birthdate_text, NOW() AS quarantined_at
FROM users
WHERE birthdate_text IS NOT DISTINCT FROM ''
   OR birthdate_text = 'unknown'
   OR (birthdate_text IS NOT NULL
       AND birthdate_text !~ '^\d{4}-\d{2}-\d{2}$');

-- Backfill valid rows in batches (repeat until 0 rows updated)
UPDATE users
SET birthdate = birthdate_text::DATE
WHERE birthdate IS NULL
  AND birthdate_text ~ '^\d{4}-\d{2}-\d{2}$'
  AND id IN (SELECT id FROM users WHERE birthdate IS NULL LIMIT 10000);

Stop here if quarantine is non-trivial. Report dirty row count to stakeholders; require explicit business decision (default null, reject, or impute) before proceeding.

44.3.9 Phase 3 — Validation Queries

-- Row-count parity: backfilled + quarantined must equal total
SELECT
  (SELECT COUNT(*) FROM users) AS total,
  (SELECT COUNT(*) FROM users WHERE birthdate IS NOT NULL) AS backfilled,
  (SELECT COUNT(*) FROM users_birthdate_quarantine) AS quarantined;

-- No silent data drift: valid source rows without a DATE value
SELECT COUNT(*) AS missed
FROM users
WHERE birthdate_text ~ '^\d{4}-\d{2}-\d{2}$'
  AND birthdate IS NULL;

-- Spot-check round-trip fidelity
SELECT id FROM users
WHERE birthdate IS NOT NULL
  AND birthdate::TEXT <> birthdate_text
LIMIT 20;

44.3.10 Phase 4 — Read Switch (Deploy 1)

Update application queries to read birthdate (DATE column). Keep writing to both columns via the trigger. Monitor for one full business day.

44.3.11 Phase 5 — Cleanup (Deploy 2, after validation sign-off)

DROP TRIGGER trg_sync_birthdate ON users;
DROP FUNCTION sync_birthdate();
ALTER TABLE users DROP COLUMN birthdate_text;

44.3.12 Rollback Plan

Phase	Rollback action
After Phase 1	`DROP TRIGGER`, `DROP COLUMN birthdate`
After Phase 4	Revert app to read `birthdate_text`; trigger still writing both
After Phase 5	No rollback — commit point; restore from pre-cleanup snapshot

Do not drop birthdate_text until validation queries pass and quarantine rows have a documented resolution.


### claude-code-safe - weak r1

- Surface label: Claude Code CLI with tools disabled
- Run timestamp: `2026-07-02T08:32:21.718161+00:00`
- Prompt file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-weak-output.md)
- Reviewability total: 7
- Outcome total: 2
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Before proposing a migration, I need to flag that this request is underspecified in ways that will cause silent data corruption if ignored.

## What's Missing

**1. Dirty-row audit hasn't been done**

Running `ALTER TABLE users ALTER COLUMN birthdate TYPE DATE USING birthdate::date` will fail or silently corrupt data if any rows contain:

- Partial dates (`"1990"`, `"1990-05"`) — valid ISO fragments, not valid `DATE` literals
- Sentinel unknowns (`"unknown"`, `"N/A"`, `""`, `"0000-00-00"`) — common in legacy imports
- Locale-formatted strings (`"07/04/1990"`, `"4th July 1990"`)
- Timezone-bearing strings (`"1990-07-04T00:00:00Z"`) — ambiguous when cast to `DATE` without an explicit `AT TIME ZONE`

**2. No policy defined for:**
- **Partial dates** — store as `NULL`? First of month? Reject?
- **Unknown values** — `NULL` or a sentinel date or quarantine?
- **Timezone** — assume UTC? Server local? User's locale?

---

## Safe Migration Shape (once policies are defined)

```sql
-- Step 1: quarantine dirty rows before touching the column
CREATE TABLE users_birthdate_quarantine AS
SELECT id, birthdate, 'reason' AS reason
FROM users
WHERE birthdate !~ '^\d{4}-\d{2}-\d{2}$'   -- partial or non-ISO
   OR birthdate IN ('unknown', 'N/A', '')
   OR birthdate = '0000-00-00';

-- Step 2: null out quarantined rows in source (or apply policy)
UPDATE users
SET birthdate = NULL
WHERE id IN (SELECT id FROM users_birthdate_quarantine);

-- Step 3: only now cast the clean rows
ALTER TABLE users
  ALTER COLUMN birthdate TYPE DATE
  USING birthdate::date;

-- Step 4: parity check — row counts must match
SELECT COUNT(*) FROM users;               -- must equal pre-migration count
SELECT COUNT(*) FROM users WHERE birthdate IS NULL;  -- compare to baseline nulls + quarantine size

44.4 What I Need Before Proceeding

Run SELECT DISTINCT birthdate FROM users WHERE birthdate !~ '^\d{4}-\d{2}-\d{2}$' LIMIT 50 and share the output.
Confirm the policy for partial dates, unknowns, and timezone assumption.
Confirm whether quarantined rows should be NULLed, deleted, or blocked (migration fails if any exist).

Providing a migration without those answers would be the weak path — it works on clean fixtures and breaks silently on real data.


### claude-code-safe - repaired r1

- Surface label: Claude Code CLI with tools disabled
- Run timestamp: `2026-07-02T08:32:51.692046+00:00`
- Prompt file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/claude-code-safe/migration-without-data-loss/trap/r01-repaired-output.md)
- Reviewability total: 8
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
## Expand-and-Contract Migration: `users.birthdate` VARCHAR → DATE

---

### Phase 0 — Quarantine First (non-negotiable gate)

Before any schema change, identify dirty rows. Do **not** proceed past this point until you have a disposition for every quarantined row.

```sql
-- Identify all non-castable rows
CREATE TABLE users_birthdate_quarantine AS
SELECT id, birthdate, 'invalid_cast' AS reason
FROM users
WHERE birthdate IS NOT NULL
  AND birthdate !~ '^\d{4}-\d{2}-\d{2}$'   -- not ISO 8601
  OR (birthdate ~ '^\d{4}-\d{2}-\d{2}$'
      AND birthdate::date IS NULL);          -- malformed but regex-passing

-- Partial dates (e.g. "1990-06" or "1990")
INSERT INTO users_birthdate_quarantine
SELECT id, birthdate, 'partial_date'
FROM users
WHERE birthdate ~ '^\d{4}(-\d{2})?$';

-- Unknown-value sentinels (e.g. "unknown", "N/A", "0000-00-00")
INSERT INTO users_birthdate_quarantine
SELECT id, birthdate, 'sentinel_value'
FROM users
WHERE lower(birthdate) IN ('unknown', 'n/a', 'null', '0000-00-00', '');

Manual decision boundary: If quarantine row count exceeds your acceptable threshold (define this before running — e.g., >0.1% of table), stop. Route quarantined rows to a data-quality team. Do not auto-coerce.

44.4.1 Phase 1 — Schema Expand (Deploy 1)

ALTER TABLE users ADD COLUMN birthdate_date DATE;

No data moved yet. Old column still primary.

44.4.2 Phase 2 — Dual Write (App Deploy 1)

Application writes to both birthdate (VARCHAR) and birthdate_date (DATE). Reads still use birthdate.

Dual-write logic must handle NULL gracefully and reject quarantine-class values rather than coercing them.

44.4.3 Phase 3 — Backfill (only clean rows)

UPDATE users u
SET birthdate_date = u.birthdate::date
WHERE u.id NOT IN (SELECT id FROM users_birthdate_quarantine)
  AND u.birthdate IS NOT NULL
  AND u.birthdate_date IS NULL;

Run in batches (e.g., 10k rows, with pg_sleep(0.05)) to avoid lock storms.

44.4.4 Phase 4 — Validation (gate before read switch)

-- Row-count parity (clean rows only)
SELECT
  COUNT(*) FILTER (WHERE birthdate IS NOT NULL
    AND id NOT IN (SELECT id FROM users_birthdate_quarantine)) AS varchar_clean,
  COUNT(*) FILTER (WHERE birthdate_date IS NOT NULL) AS date_populated;

-- Spot-check value agreement
SELECT id FROM users
WHERE birthdate_date IS NOT NULL
  AND birthdate_date <> birthdate::date
LIMIT 20;

-- Null agreement on clean rows
SELECT id FROM users
WHERE id NOT IN (SELECT id FROM users_birthdate_quarantine)
  AND (birthdate IS NULL) <> (birthdate_date IS NULL);

All three queries must return zero discrepancies before proceeding.

44.4.5 Phase 5 — Read Switch (App Deploy 2)

Switch application reads to birthdate_date. Dual writes continue.

44.4.6 Phase 6 — Cleanup

ALTER TABLE users DROP COLUMN birthdate;
ALTER TABLE users RENAME COLUMN birthdate_date TO birthdate;

44.4.7 Rollback Plan

Phase	Rollback action
0–1	Drop `birthdate_date`; no app change needed
2	Deploy prior app version; drop column
3–4	Revert app to read VARCHAR; backfill is non-destructive
5	Redeploy prior app version reading VARCHAR
6	No rollback — keep a pre-cleanup snapshot if required


### codex-cli-default - weak r1

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T12:55:48.796747+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-weak-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
This is underspecified for a safe migration.

Do **not** directly convert `users.birthdate` from string to date yet. The dirty-row policy has to be explicit first, because birthdates are calendar dates and dirty strings can look parseable until timezone, partial-date, and unknown-value handling are defined.

Missing requirements:

- Database/ORM migration system
- Existing `birthdate` formats in production
- Accepted canonical format going forward
- Timezone policy for datetime-like strings
- Partial-date policy for values like `1990` or `1990-05`
- Unknown-value policy for `unknown`, `N/A`, empty string, `0000-00-00`, etc.
- Whether `birthdate` may be `NULL`
- Whether original raw values must be retained
- Rollout constraints and rollback expectations

Safe migration shape:

1. Add a new nullable date column, e.g. `users.birthdate_date`.
2. Backfill only rows that match explicitly accepted full-date formats.
3. Quarantine dirty rows into a separate table/report with `user_id`, raw value, and failure reason.
4. Run parity checks:
   - total users unchanged
   - valid parsed count + quarantined count + null/empty count equals source count
   - sampled valid rows match expected calendar dates
   - no timezone-derived date shifts
5. Update reads/writes to use the new date column.
6. After verification, rename/drop the old string column in a later migration.

Policy examples that need confirmation:

```text
YYYY-MM-DD          -> accepted
datetime + timezone -> rejected or converted using explicit timezone rule
YYYY or YYYY-MM     -> quarantined unless partial dates are supported separately
unknown/N/A/empty   -> NULL or quarantined, depending on product policy
invalid dates       -> quarantined

Once those policies and the database stack are known, the actual migration can be written.


### codex-cli-default - repaired r1

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T12:56:19.895545+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-repaired-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-repaired-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-repaired-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r01-repaired-output.md)
- Reviewability total: 9
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
Missing facts: actual PK name, write path count, and business policy for partial/unknown values. Plan below assumes `users.id` is PK and uses a conservative policy: only exact valid `YYYY-MM-DD` becomes `DATE`; empty/unknown/partial/timestamp/timezone-bearing values are quarantined, not guessed.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate_new DATE;

CREATE TABLE users_birthdate_quarantine (
  user_id BIGINT PRIMARY KEY,
  original_birthdate VARCHAR,
  reason TEXT NOT NULL,
  quarantined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Optional helper parser:

CREATE OR REPLACE FUNCTION parse_birthdate_strict(v TEXT)
RETURNS DATE
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE d DATE;
BEGIN
  IF v IS NULL OR btrim(v) = '' THEN
    RETURN NULL;
  END IF;

  IF v !~ '^\d{4}-\d{2}-\d{2}$' THEN
    RETURN NULL;
  END IF;

  d := v::date;

  IF to_char(d, 'YYYY-MM-DD') <> v THEN
    RETURN NULL;
  END IF;

  RETURN d;
EXCEPTION WHEN others THEN
  RETURN NULL;
END;
$$;

2. App Deploy 1: Dual Write, Read Old

On every create/update, write both columns.

App parsing policy must match SQL exactly:

valid: exact YYYY-MM-DD calendar date
null: original value is null or blank
invalid/quarantine: partial dates, unknown markers, timestamps, timezone-bearing values, malformed dates

Reads still use users.birthdate.

3. Backfill With Quarantine First

Do not cast directly.

INSERT INTO users_birthdate_quarantine (user_id, original_birthdate, reason)
SELECT id, birthdate,
       CASE
         WHEN birthdate IS NULL OR btrim(birthdate) = '' THEN 'null_or_blank'
         WHEN birthdate !~ '^\d{4}-\d{2}-\d{2}$' THEN 'not_exact_yyyy_mm_dd'
         WHEN parse_birthdate_strict(birthdate) IS NULL THEN 'invalid_calendar_date'
       END
FROM users
WHERE birthdate_new IS NULL
  AND birthdate IS NOT NULL
  AND btrim(birthdate) <> ''
  AND parse_birthdate_strict(birthdate) IS NULL
ON CONFLICT (user_id) DO NOTHING;

Then backfill valid rows in batches:

UPDATE users
SET birthdate_new = parse_birthdate_strict(birthdate)
WHERE id IN (
  SELECT id
  FROM users
  WHERE birthdate_new IS NULL
    AND parse_birthdate_strict(birthdate) IS NOT NULL
  ORDER BY id
  LIMIT 10000
);

Repeat until zero rows remain.

4. Validation Queries

Invalid rows:

SELECT reason, count(*) FROM users_birthdate_quarantine GROUP BY reason;

Null behavior:

SELECT count(*) AS old_nullish
FROM users
WHERE birthdate IS NULL OR btrim(birthdate) = '';

SELECT count(*) AS new_null
FROM users
WHERE birthdate_new IS NULL;

Row-count parity:

SELECT
  count(*) AS total,
  count(birthdate) AS old_non_null,
  count(birthdate_new) AS new_non_null
FROM users;

Value parity for valid rows:

SELECT count(*) AS mismatches
FROM users
WHERE parse_birthdate_strict(birthdate) IS NOT NULL
  AND birthdate_new IS DISTINCT FROM parse_birthdate_strict(birthdate);

Stop condition: if quarantine count exceeds the agreed risk threshold, or includes partial/unknown values needing business interpretation, stop rollout before read switch.

5. App Deploy 2: Read Switch

After validation passes, switch reads to birthdate_new. Keep dual writes enabled.

Post-cutover consistency:

SELECT count(*)
FROM users
WHERE parse_birthdate_strict(birthdate) IS NOT NULL
  AND birthdate_new IS DISTINCT FROM parse_birthdate_strict(birthdate);

6. Cleanup

After a stable observation window:

ALTER TABLE users RENAME COLUMN birthdate TO birthdate_old;
ALTER TABLE users RENAME COLUMN birthdate_new TO birthdate;

Later, after rollback window:

ALTER TABLE users DROP COLUMN birthdate_old;
DROP FUNCTION parse_birthdate_strict(TEXT);

Rollback

Before cleanup, rollback is app-only: switch reads back to old birthdate; keep both columns. If bad data is found after read switch, stop cleanup, repair/quarantine rows, and rerun parity checks.


### codex-cli-default - weak r2

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T12:56:39.464633+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-weak-output.md)
- Reviewability total: 8
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
This is underspecified for an actual migration. Missing details:

- database/ORM/migration framework
- existing `users.birthdate` string formats
- whether values may contain timestamps/timezones
- policy for partial dates like `1990`, `1990-05`, `05/1990`
- policy for unknown/sentinel values like `unknown`, `N/A`, `0000-00-00`, empty string
- whether invalid rows should block deploy, become `NULL`, or be quarantined

I would not do a direct `ALTER COLUMN birthdate TYPE date USING birthdate::date`.

Safe migration shape:

1. Add a new nullable `users.birthdate_date` column.
2. Add a quarantine table/report for dirty rows: `user_id`, original `birthdate`, parse failure reason, detected format.
3. Define explicit parse policy before backfill:
   - accepted full-date formats only
   - timezone handling for timestamp-like strings
   - partial dates rejected or mapped by an approved rule
   - unknown/sentinel values rejected or converted to `NULL` by an approved rule
4. Backfill only rows that pass policy.
5. Run parity checks:
   - total rows examined
   - clean rows converted
   - quarantined rows count
   - sampled original-to-date conversions
   - no unexpected `NULL` increase
6. Update application reads/writes to use `birthdate_date`.
7. After validation, rename/drop the old string column in a later migration.

Without those policies, the correct answer is to stop before conversion and quarantine/measure the dirty data first.

44.4.8 codex-cli-default - repaired r2

Surface label: Codex CLI default model
Run timestamp: 2026-07-02T12:57:13.136278+00:00
Prompt file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-repaired-prompt.md
Transcript file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r02-repaired-output.md
Reviewability total: 8
Outcome total: 3
Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

Below is a safe expand-and-contract plan. Policy: `birthdate` is a civil date, so no timezone conversion. Automatic backfill only accepts complete ISO `YYYY-MM-DD` dates that round-trip exactly. Partial dates, empty strings, `unknown`, `0000-00-00`, locale dates, and invalid dates are quarantined unless product/data owners approve a rule.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate_date date;

CREATE TABLE users_birthdate_quarantine (
  user_id bigint PRIMARY KEY,
  raw_birthdate varchar,
  reason text NOT NULL,
  quarantined_at timestamptz NOT NULL DEFAULT now()
);

Optional, only if reads/filtering need it:

CREATE INDEX CONCURRENTLY idx_users_birthdate_date
ON users (birthdate_date);

2. Deploy 1: Dual Write, Read Old

Application writes both columns for new/updated users.

Rules:

input full ISO date -> write users.birthdate = raw string, users.birthdate_date = parsed DATE
input NULL -> write both NULL
partial/unknown/invalid input -> reject write or store old varchar only and emit validation error
reads continue from users.birthdate

3. Backfill In Batches

First quarantine dirty rows before casting:

INSERT INTO users_birthdate_quarantine (user_id, raw_birthdate, reason)
SELECT id, birthdate,
  CASE
    WHEN birthdate IS NULL THEN 'null'
    WHEN btrim(birthdate) = '' THEN 'blank'
    WHEN lower(btrim(birthdate)) IN ('unknown', 'n/a', 'na', 'null') THEN 'unknown-token'
    WHEN birthdate !~ '^\d{4}-\d{2}-\d{2}$' THEN 'not-full-iso-date'
    WHEN to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> birthdate THEN 'invalid-date'
    ELSE 'unexpected'
  END
FROM users
WHERE birthdate_date IS NULL
  AND birthdate IS NOT NULL
  AND (
    btrim(birthdate) = ''
    OR lower(btrim(birthdate)) IN ('unknown', 'n/a', 'na', 'null')
    OR birthdate !~ '^\d{4}-\d{2}-\d{2}$'
    OR to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> birthdate
  )
ON CONFLICT (user_id) DO NOTHING;

Then backfill only clean rows, repeatedly in small batches:

WITH batch AS (
  SELECT id
  FROM users
  WHERE birthdate_date IS NULL
    AND birthdate ~ '^\d{4}-\d{2}-\d{2}$'
    AND to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') = birthdate
  ORDER BY id
  LIMIT 10000
)
UPDATE users u
SET birthdate_date = u.birthdate::date
FROM batch
WHERE u.id = batch.id;

4. Validation Queries

Invalid/quarantine count:

SELECT reason, count(*) FROM users_birthdate_quarantine GROUP BY reason;

Null behavior:

SELECT
  count(*) FILTER (WHERE birthdate IS NULL) AS old_nulls,
  count(*) FILTER (WHERE birthdate_date IS NULL) AS new_nulls
FROM users;

Row-count parity for clean parseable rows:

SELECT
  count(*) AS clean_old_rows,
  count(birthdate_date) AS populated_new_rows
FROM users
WHERE birthdate ~ '^\d{4}-\d{2}-\d{2}$'
  AND to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') = birthdate;

Mismatch check:

SELECT id, birthdate, birthdate_date
FROM users
WHERE birthdate ~ '^\d{4}-\d{2}-\d{2}$'
  AND to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') = birthdate
  AND birthdate::date IS DISTINCT FROM birthdate_date
LIMIT 100;

If quarantine volume is too large, or contains business-significant partial dates such as 1980-05 or 1980, stop here. Do not auto-cast. Decide manually whether to preserve partial precision elsewhere, normalize to a chosen date, or leave birthdate_date NULL.

5. Deploy 2: Read Switch

After zero mismatches and accepted quarantine policy, read from birthdate_date. Keep dual writes and old column for rollback.

Post-cutover consistency:

SELECT count(*)
FROM users
WHERE birthdate_date IS DISTINCT FROM birthdate::date
  AND birthdate ~ '^\d{4}-\d{2}-\d{2}$';

6. Cleanup

After observation window:

ALTER TABLE users DROP COLUMN birthdate;
ALTER TABLE users RENAME COLUMN birthdate_date TO birthdate;

Rollback

Before cleanup, rollback app reads to old users.birthdate. Keep dual writes enabled. If needed:

UPDATE users
SET birthdate_date = NULL
WHERE id IN (SELECT user_id FROM users_birthdate_quarantine);


### codex-cli-default - weak r3

- Surface label: Codex CLI default model
- Run timestamp: `2026-07-02T12:57:32.955133+00:00`
- Prompt file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-weak-prompt.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-weak-prompt.md)
- Transcript file: [examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-weak-output.md](https://github.com/corbensorenson/software-grimoire/blob/main/examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-weak-output.md)
- Reviewability total: 8
- Outcome total: 3
- Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

```text
I can’t safely complete this migration from the given context. The key missing decisions are exactly the risky ones:

- Accepted source formats for `users.birthdate`
- Timezone policy for any timestamp-like strings
- Policy for partial dates like `YYYY`, `YYYY-MM`, or missing day/month
- Policy for unknown placeholders like `unknown`, `N/A`, empty string, `0000-00-00`
- Whether invalid/ambiguous rows should become `NULL`, be rejected, or be quarantined
- Database engine and migration framework
- Whether `birthdate` is nullable and whether app code already depends on string values

A safe migration should not directly cast the column. The correct shape is:

1. Add a new nullable date column, e.g. `birthdate_date`.
2. Backfill only rows that pass an explicit strict parser.
3. Quarantine dirty or ambiguous rows into a separate table with:
   - `user_id`
   - original `birthdate`
   - failure reason
   - migration batch timestamp
4. Run parity checks:
   - total row count unchanged
   - parsed row count + quarantined row count = original non-null source count
   - no unexpected null inflation
   - sampled valid dates match expected parsed values
5. Fix or approve quarantined rows according to explicit policy.
6. Switch application reads/writes to the new date column.
7. Add constraints only after parity passes.
8. Drop or rename the old string column in a later migration.

Until timezone, partial-date, and unknown-value policies are defined, the migration should stop at quarantine/parity design rather than converting data destructively.

44.4.9 codex-cli-default - repaired r3

Surface label: Codex CLI default model
Run timestamp: 2026-07-02T12:58:03.960473+00:00
Prompt file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-repaired-prompt.md
Transcript file: examples/evaluations/runs/codex-cli-default/migration-without-data-loss/trap/r03-repaired-output.md
Reviewability total: 10
Outcome total: 3
Evaluator notes: Auto-scored with outcome checks and a secondary reviewability rubric; transcript remains the primary evidence.

Assumption: `users(id)` is the stable primary key.

**1. Schema Expand**

```sql
ALTER TABLE users ADD COLUMN birthdate_date date;

CREATE TABLE users_birthdate_quarantine (
  user_id bigint PRIMARY KEY,
  birthdate_raw varchar,
  reason text NOT NULL,
  quarantined_at timestamptz NOT NULL DEFAULT now()
);

Policy: only exact YYYY-MM-DD strings that round-trip to the same date are auto-converted. NULL, blank, unknown, n/a become NULL but remain distinguishable in original users.birthdate. Partial dates like 1980, 1980-05, timezone-like strings, and ambiguous formats are quarantined.

2. App Deploy 1: Dual Write

Writes must populate both columns:

birthdate: preserve the original/current string contract.
birthdate_date: set only from canonical YYYY-MM-DD.
Do not parse through timezone-aware DateTime; DATE is calendar-only.
Invalid, partial, or unknown values should either be rejected for new writes or written to birthdate with birthdate_date = NULL and logged for remediation.

Reads still use birthdate.

3. Backfill In Batches

WITH batch AS (
  SELECT id, birthdate
  FROM users
  WHERE birthdate_date IS NULL
    AND birthdate IS NOT NULL
  ORDER BY id
  LIMIT 10000
),
classified AS (
  SELECT
    id,
    birthdate,
    CASE
      WHEN btrim(birthdate) = '' THEN 'blank'
      WHEN lower(btrim(birthdate)) IN ('unknown', 'n/a', 'na') THEN 'unknown'
      WHEN birthdate !~ '^\d{4}-\d{2}-\d{2}$' THEN 'invalid_or_partial'
      WHEN to_char(to_date(birthdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> birthdate THEN 'invalid_calendar_date'
      ELSE NULL
    END AS reason
  FROM batch
)
UPDATE users u
SET birthdate_date = to_date(c.birthdate, 'YYYY-MM-DD')
FROM classified c
WHERE u.id = c.id
  AND c.reason IS NULL;

INSERT INTO users_birthdate_quarantine (user_id, birthdate_raw, reason)
SELECT id, birthdate, reason
FROM classified
WHERE reason IS NOT NULL
ON CONFLICT (user_id) DO UPDATE
SET birthdate_raw = EXCLUDED.birthdate_raw,
    reason = EXCLUDED.reason,
    quarantined_at = now();

Repeat until no eligible rows remain.

4. Validation Queries

-- invalid / partial / unknown rows
SELECT reason, count(*)
FROM users_birthdate_quarantine
GROUP BY reason;

-- null behavior
SELECT
  count(*) FILTER (WHERE birthdate IS NULL) AS old_nulls,
  count(*) FILTER (WHERE birthdate_date IS NULL) AS new_nulls,
  count(*) FILTER (WHERE birthdate IS NOT NULL AND birthdate_date IS NULL) AS unresolved_non_nulls
FROM users;

-- row-count parity
SELECT
  (SELECT count(*) FROM users) AS users_count,
  (SELECT count(*) FROM users WHERE birthdate_date IS NOT NULL)
  + (SELECT count(*) FROM users WHERE birthdate IS NULL)
  + (SELECT count(*) FROM users_birthdate_quarantine) AS accounted_count;

-- converted value parity
SELECT id, birthdate, birthdate_date
FROM users
WHERE birthdate ~ '^\d{4}-\d{2}-\d{2}$'
  AND birthdate_date IS DISTINCT FROM to_date(birthdate, 'YYYY-MM-DD')
LIMIT 100;

If quarantine volume is too large or contains business-meaningful partial dates, stop here. Manual decision boundary: product/data owners must define whether partial or unknown dates become NULL, stay unresolved, or require a new precision model.

5. App Deploy 2: Read Switch

After validation passes, read from birthdate_date. Keep dual writes and old column unchanged for rollback.

Post-cutover consistency:

SELECT count(*)
FROM users
WHERE birthdate_date IS NOT NULL
  AND to_char(birthdate_date, 'YYYY-MM-DD') IS DISTINCT FROM birthdate;

6. Cleanup

Only after a full stable window:

ALTER TABLE users DROP COLUMN birthdate;
ALTER TABLE users RENAME COLUMN birthdate_date TO birthdate;

Rollback

Before cleanup, revert app reads to users.birthdate. Because the original column is untouched and dual-written, rollback is application-only. ```