---
apiVersion: data-spec/v1
id: autonumber-v2-drop-legacy-20260419
description: "Autonumber v2 cutover — drop the 4 retired legacy tables after snapshotting their rows to __bak_*_v1 (2-week retention)"
title: "Autonumber v2: drop legacy tables"
issue: ""
created: 2026-04-19
scope: both
status: pending-staging
tables_modified:
  - _System_AutoNumber
  - _System_CompoundAutoNumber
  - _System_IdentifierRule
  - _System_IdentifierSeq
tables: [_System_AutoNumber, _System_CompoundAutoNumber, _System_IdentifierRule, _System_IdentifierSeq]
idempotent: true
risk: high
reviewers: ["@codeowners"]
depends_on:
  - autonumber-v2-seed-20260418
---

# Autonumber v2: Drop Legacy Tables

> ### NON-IDEMPOTENT AT SQL LEVEL — ONE-SHOT APPLY (R5-H2)
>
> This spec is **SQL-level non-idempotent**: step 1's backup statements
> (`CREATE TABLE IF NOT EXISTS __bak__System_AutoNumber_v1 LIKE _System_AutoNumber`)
> read the live legacy tables. Once step 2 has dropped them, a direct
> re-execution of the SQL body fails with `ERROR 1146 "Table ...
> doesn't exist"` on the very first statement.
>
> Idempotency is provided **at the applier layer, not at the SQL layer**.
> `data-spec-apply` tracks a `status` state machine in this file's
> frontmatter: after the first successful apply the applier flips
> `status: pending-*` → `status: applied`, and any subsequent invocation
> (either manual re-run or an automated sweep) is a non-error skip
> (`.agent/skills/data-spec-apply/SKILL.md §Status Model`, rules RG-2 /
> applier assertion).
>
> **Do NOT** re-execute the SQL body manually (`mysql < drop-legacy.md`,
> copy-paste the `sql` block, etc.). Always route through the applier so
> the status gate holds.
>
> If recovery is needed, follow the **Rollback** section below — do not
> rely on re-running this spec. The `__bak__*_v1` snapshots preserve full
> structural DDL (Codex V2-Codex-H4) so restoration is guaranteed
> structurally complete until the 2-week retention window closes
> (2026-05-03).

> ### OPERATOR-ENFORCED PRECONDITION — NO AUTOMATED GATE
>
> **This spec has no machine-enforced binary-version or deployment-state
> check.** `data-spec-apply` currently ignores unknown frontmatter keys;
> earlier drafts included `depends_on_commit_sha` / `precondition_check` /
> `cutover_order_warning` keys, but those were prose — the applier never
> read them, and the referenced `_System_Deployment` table does not
> exist. Keeping them in the frontmatter was misleading, so they have
> been removed (V2-R3-C2).
>
> **Apply this spec ONLY after:**
>
> 1. PR for `feat/autonumber-v2-cutover` is merged to `main`.
> 2. The Docker image that contains the merge commit is deployed to
>    **100 %** of production backend pods (not canary, not a subset).
> 3. Old-binary drain confirmed: **≥ 2 hours** observed with no pods
>    running pre-cutover binaries serving traffic (check via your
>    deployment platform's pod-version dashboard — AWS ECS task
>    revision, k8s Deployment `spec.template.metadata.labels.commit`,
>    whichever is canonical for the cluster).
>
> **Apply order:** staging → verify counts / backups → prod.
>
> If this spec is applied before every pod is on the cutover binary,
> any still-old pod's fallback read of `_System_AutoNumber` (and the
> three other legacy tables) will 404 on the next autonumber assignment
> attempt → user-visible data loss. Recovery requires restoring from
> `__bak__System_*_v1` and redeploying the reverted binary (see
> Rollback below). **Do not skip the drain wait.**

## Summary

The Wave 4 cutover (PRD §7.2) retires the 4 legacy autonumber/identifier tables. Their contents have already been migrated into `_System_AutoNumberCounter` by `SeedAutoNumberCounters` (scope-aware GREATEST per PRD §8.2 step 3.e + Round-2 fixpack F8). This spec:

1. **Snapshots** each legacy table to `__bak_<name>_v1` — 2-week retention per PRD §8.5.
2. **DROPs** the legacy tables.

This is NOT a rollback spec (`incident_ticket` is intentionally blank). After the 2-week retention window, a follow-up data-spec may drop the `__bak_*_v1` tables.

## Precondition

`autonumber-v2-seed-20260418` (Lane C1 format seeds) MUST already be applied. `SeedAutoNumberCounters` MUST have run to completion (i.e., the `_System_AutoNumberCounter` table is populated). Both are phase5 outputs of bootstrap apply.

## Changes

### 0. Machine-enforced cutover-readiness precondition (Codex round-8 V2 CRITICAL)

> **Self-guarding SQL.** The operator-procedure banner above documented
> the drain requirement as prose — the applier never enforced it. If a
> rushed operator invoked this spec before the cutover binary rolled
> to every pod, old-binary reads against the about-to-be-dropped
> tables would 404 and lose user-visible inserts.
>
> The SELECT below fails loudly with a structured MySQL error if the
> cutover metadata is not in place. It's not a perfect drain check
> (that needs per-pod introspection the DB can't see), but it catches
> the most common footgun: apply-before-setup. Two failure cases
> trigger:
>   1. `_System_AutoNumberCounter` is missing or empty — Wave 4 Lane D
>      never seeded any counter rows, so `cmd/setup apply` has not
>      materialized the v2 schema on this DB.
>   2. `_System_Field.auto_number_format` cohort is below 100 — the
>      standard 132-seed cutover (V8 floor in autonumber_verify.go) has
>      not landed, so post-drop the engine will have no rules to apply.
>
> The SELECT uses `IF(..., 1, (SELECT <invalid>))` — on precondition
> fail it evaluates the invalid subquery and MySQL/TiDB raises a
> parse-level error that the applier surfaces verbatim. The spec
> aborts before step 1 touches any table.

```sql
-- Precondition: _System_AutoNumberCounter table exists and has rows.
-- If the counter table is missing the second SELECT from
-- information_schema returns 0 and the outer IF evaluates the sentinel
-- "cutover_readiness_check_FAILED_counter_table_missing_or_empty"
-- which is an unresolvable column reference → query errors out.
SELECT IF(
  (SELECT COUNT(*) FROM information_schema.tables
     WHERE table_schema = DATABASE()
       AND table_name = '_System_AutoNumberCounter') = 1
  AND (SELECT COUNT(*) FROM `_System_AutoNumberCounter`) > 0,
  1,
  cutover_readiness_check_FAILED_counter_table_missing_or_empty
) AS precondition_counter_check;

-- Precondition: _System_Field has >= 100 autonumber-configured rows.
-- Matches V8 gate (MinExpectedAutoNumberFieldsV8) in
-- backend/internal/bootstrap/autonumber_verify.go.
SELECT IF(
  (SELECT COUNT(*) FROM `_System_Field`
     WHERE `auto_number_format` IS NOT NULL
       AND `auto_number_format` != '') >= 100,
  1,
  cutover_readiness_check_FAILED_autonumber_format_cohort_below_floor
) AS precondition_format_cohort_check;
```

### 1. Snapshot legacy tables to `__bak_*_v1`

> **Round-5 Codex V2-Codex-H4:** the previous spec used
> `CREATE TABLE ... AS SELECT *` which in MySQL/TiDB copies data + a
> minimal column list ONLY — **primary keys, secondary indexes,
> UNIQUE constraints, CHECK constraints, and AUTO_INCREMENT bindings
> are NOT preserved**. A rollback attempt from that backup would
> re-create the legacy tables as heap tables, breaking the unique
> lookups (`UNIQUE KEY uk_obj_field (ObjectApiName, FieldApiName)`) the
> runtime depended on and silently producing duplicate legacy rows
> under concurrent pre-cutover binaries.
>
> The two-step `CREATE TABLE ... LIKE` + `INSERT ... SELECT` pattern
> below preserves the full structural DDL (PKs, indexes, UNIQUEs,
> column defaults) — rollback from these backups will recreate the
> tables at structural parity.

```sql
-- Idempotent: CREATE TABLE IF NOT EXISTS + INSERT ... SELECT ... guarded
-- by a NOT-EXISTS sub-clause. First run copies schema + rows. Subsequent
-- runs observe the backup already exists (IF NOT EXISTS short-circuits)
-- and the INSERT's NOT EXISTS ON the target skips re-copy so the
-- original snapshot is preserved.
--
-- IMPORTANT: Apply this in SEQUENCE with the DROPs in step 2. The
-- INSERTs below read from the live legacy tables; if this spec is
-- re-applied after the DROPs succeeded, the INSERT source table is
-- missing — the WHERE-gate on row count (`SELECT COUNT(*) FROM ...`)
-- protects against that: the INSERT is only run when both the backup
-- is empty AND the source exists.

CREATE TABLE IF NOT EXISTS `__bak__System_AutoNumber_v1` LIKE `_System_AutoNumber`;
CREATE TABLE IF NOT EXISTS `__bak__System_CompoundAutoNumber_v1` LIKE `_System_CompoundAutoNumber`;
CREATE TABLE IF NOT EXISTS `__bak__System_IdentifierRule_v1` LIKE `_System_IdentifierRule`;
CREATE TABLE IF NOT EXISTS `__bak__System_IdentifierSeq_v1` LIKE `_System_IdentifierSeq`;

-- Populate backups if (and only if) they are empty. A second apply
-- finds non-empty backups and is a no-op — preserves the original
-- snapshot even when the source live table has been mutated between
-- runs (e.g. an incident-recovery pre-drop edit).
INSERT INTO `__bak__System_AutoNumber_v1`
  SELECT * FROM `_System_AutoNumber`
  WHERE NOT EXISTS (SELECT 1 FROM `__bak__System_AutoNumber_v1` LIMIT 1);
INSERT INTO `__bak__System_CompoundAutoNumber_v1`
  SELECT * FROM `_System_CompoundAutoNumber`
  WHERE NOT EXISTS (SELECT 1 FROM `__bak__System_CompoundAutoNumber_v1` LIMIT 1);
INSERT INTO `__bak__System_IdentifierRule_v1`
  SELECT * FROM `_System_IdentifierRule`
  WHERE NOT EXISTS (SELECT 1 FROM `__bak__System_IdentifierRule_v1` LIMIT 1);
INSERT INTO `__bak__System_IdentifierSeq_v1`
  SELECT * FROM `_System_IdentifierSeq`
  WHERE NOT EXISTS (SELECT 1 FROM `__bak__System_IdentifierSeq_v1` LIMIT 1);
```

### 2. Drop the legacy tables

```sql
DROP TABLE IF EXISTS `_System_AutoNumber`;
DROP TABLE IF EXISTS `_System_CompoundAutoNumber`;
DROP TABLE IF EXISTS `_System_IdentifierRule`;
DROP TABLE IF EXISTS `_System_IdentifierSeq`;
```

## Idempotency

Idempotency is provided by the **applier's status gate**, not by the SQL
body. See the "NON-IDEMPOTENT AT SQL LEVEL" banner above for the full
rationale (R5-H2).

The SQL statements use `IF NOT EXISTS` (CREATE) / `IF EXISTS` (DROP) for
partial-failure robustness within a single apply attempt — e.g., if a
crash lands between the four DROPs in step 2, a follow-up apply on the
same pending status cleanly finishes without erroring on the already-
dropped tables. Those guards do NOT make the full SQL body safe to
re-execute after step 2 completes: step 1's `CREATE TABLE ... LIKE
_System_AutoNumber` requires the source, which is gone. The frontmatter's
`idempotent: true` reflects the applier-layer guarantee (status=applied
is a no-op on retry), not a claim of stand-alone SQL replay safety.

## Verification

After apply, verify:

- `SELECT COUNT(*) FROM __bak__System_AutoNumber_v1;` returns the row count the legacy table held at snapshot time.
- `SHOW TABLES LIKE '_System_AutoNumber';` returns an empty result set.
- Application code no longer references the dropped tables (enforced at compile time — see `backend/internal/infrastructure/persistence/metadata_schema.go`, which no longer reads `_System_AutoNumber`).

## Rollback

This spec is NOT directly reversible: the live tables are gone. Because
step 1 now uses `CREATE TABLE LIKE` (Codex V2-Codex-H4), the
`__bak_*_v1` tables carry the full structural DDL (PKs, indexes,
UNIQUEs, defaults). Structural parity is guaranteed on restore — you
do **not** need to re-read `system_tables.json` to reconstruct DDL.

To restore, you MUST:

1. Recreate each legacy table with identical structure and repopulate:

   ```sql
   CREATE TABLE `_System_AutoNumber` LIKE `__bak__System_AutoNumber_v1`;
   INSERT INTO `_System_AutoNumber` SELECT * FROM `__bak__System_AutoNumber_v1`;
   CREATE TABLE `_System_CompoundAutoNumber` LIKE `__bak__System_CompoundAutoNumber_v1`;
   INSERT INTO `_System_CompoundAutoNumber` SELECT * FROM `__bak__System_CompoundAutoNumber_v1`;
   CREATE TABLE `_System_IdentifierRule` LIKE `__bak__System_IdentifierRule_v1`;
   INSERT INTO `_System_IdentifierRule` SELECT * FROM `__bak__System_IdentifierRule_v1`;
   CREATE TABLE `_System_IdentifierSeq` LIKE `__bak__System_IdentifierSeq_v1`;
   INSERT INTO `_System_IdentifierSeq` SELECT * FROM `__bak__System_IdentifierSeq_v1`;
   ```

   Alternative (zero-data-movement, same structural guarantee):

   ```sql
   RENAME TABLE `__bak__System_AutoNumber_v1` TO `_System_AutoNumber`;
   -- (repeat for all four)
   ```

   Use `RENAME TABLE` when you accept losing the backup in the same
   step; use the `CREATE TABLE LIKE + INSERT` pair when you want the
   backup to remain available for further forensics.

2. Revert the Wave 4 cutover Git commit (which re-adds the tables to
   `system_tables.json` and re-introduces the Go code paths that read
   them).

3. Re-deploy the reverted binary.

Step 2 is the hard constraint. Until the 2-week retention window
closes, step 1 is guaranteed to succeed because `__bak_*_v1` tables
are retained and structurally complete.

After the 2-week window (2026-05-03), a follow-up data-spec MAY drop
the backups. Before then, any rollback can fully restore both data and
structure.