# DropTable / strict-create atomicity (#3484)

## Invariant

An object API name is creatable through interactive authoring
(`MetadataService.CreateSchema` / `CreateSchemaOptimized` ->
`CreateTableWithStrictMetadata`) **iff neither live metadata NOR a physical
table of that name exists**. Equivalently: strict creation may bind new
metadata ONLY to a physical table the same call just created.

This is enforced atomically by the DDL itself. The strict path issues a plain
`CREATE TABLE` (no `IF NOT EXISTS`), so a pre-existing table makes the CREATE
fail with MySQL errno 1050, mapped to a fail-closed `ConflictError` carrying the
reconcile remediation. There is no separate `INFORMATION_SCHEMA` probe on the
create side, so no TOCTOU window and no second read path for the "table exists"
fact.

## Placement

`CreateTableWithStrictMetadata` (`nexuscrm-persistence/schema_ddl_ops.go`) is the
single strict-create chokepoint. Current-tree callers are exactly
`metadata_schema.go` `CreateSchema` and `CreateSchemaOptimized` via
`schema_manager.go`. Nothing in controlplane / backend / batch calls it.

The exported `CreatePhysicalTable` / `BatchCreatePhysicalTables` keep
`CREATE TABLE IF NOT EXISTS` byte-identical, so bootstrap / pack-install /
tenant-overlay idempotent re-runs are preserved (the critical edge).

## Create-path behavior

Fail closed, never auto-reconcile. On a lingering table (metadata absent,
physical table present) `CreateSchema` returns 409 / `ConflictError`; the
remediation ("retry DELETE to reconcile the orphan, then re-create") lives in
the message. A pre-commit strict-metadata failure compensates by dropping the
just-created table so an in-band retry starts clean. The compensating drop fires
**only** on failures proven to occur BEFORE `tx.Commit` is invoked — a commit
error is ambiguous on TiDB (the commit may have durably landed while the ack was
lost), so dropping on commit error would manufacture the live-metadata-without-a-
table state this change exists to abolish.

## Delete-side reconcile guards

`DeleteSchema`'s no-live-metadata branch may DROP a stranded orphan table, fenced
by four corroborations (`shouldReconcileOrphanTable`):

1. **Managed substrate** — `constants.IsManagedSystemTable` (case-insensitive on
   both the `_System_` prefix and the managed list). `AllSystemTableNames` was
   regenerated against the live schema catalog (+28 tables) and is bound by
   `TestAllSystemTableNamesCoversSchemaCatalog` so the drift cannot recur.
2. **Cache load** — fail closed if the core schema cache cannot initialize
   (`GetSchemas` would otherwise swallow the failure into an empty slice).
3. **ReadSource aliasing** — refuse if any live object resolves its physical read
   table to the target name.
4. **Drop-pending tombstone (provenance proof)** — the destructive drop is
   authorized ONLY by a `_System_Table` row with `IsDeleted=1`
   (`HasDropPendingTombstone`), which `markDropPendingTombstone` writes
   **exclusively when a physical DROP failed** after the metadata purge. A
   successful drop deletes the registry row inside the metadata transaction and
   never writes the marker — there is NO post-success cleanup step whose failure
   could strand an authorization, so the stale-authorization class is
   structurally impossible (this supersedes both the system-column shape
   heuristic, defeated by a same-shape `CREATE TABLE ... LIKE` copy, and the
   keep-row-until-success variant, whose post-success cleanup failure left a
   stale row with authorization power). A raw operator/ETL/backup table sharing
   the name never has the marker and is never touched: the delete stays a
   fail-closed 404 no-op, exactly the base behavior. Fail closed on probe error.

   Residual (fail-SAFE direction only): if the marker write itself fails — or
   the process crashes between the metadata commit and the marker write — the
   orphan table is not reconcilable through the API (reconcile refuses, 404);
   the strict-create 409 remediation routes the operator to manual cleanup.
   Failure degrades toward less self-healing, never toward false authorization.

## FK-durable-drop position

`DropTable` gains a phase-0 inbound-FK pre-check
(`INFORMATION_SCHEMA.KEY_COLUMN_USAGE`, self-FKs excluded) BEFORE any metadata
mutation, returning a `ConflictError` naming the referencing constraint so an
FK-blocked delete leaves the object fully live instead of manufacturing a durable
orphan. `dropPhysicalColumnIfPresent` drops FK constraints before `DROP COLUMN`,
so the delete-referencing-field -> delete-object recovery loop completes through
the platform (no 1828 dead-end, no hand-SQL).

## Boundaries

- **Single-writer authoring**: `ms.mu` serializes the reconcile against
  `CreateSchema` in-process and the strict plain-CREATE closes cross-replica
  create-vs-create. The residual reconcile-DROP-vs-remote-create window cannot be
  closed transactionally (TiDB DDL auto-commits); this PR assumes single-writer
  interactive authoring. Follow-up: a DB-level authoring name lock.
- **Controlplane adoption — CLOSED for delete residue**: pack-install /
  tenant-overlay keep `CREATE TABLE IF NOT EXISTS` + metadata upsert for resume
  idempotence, but the shared entry (`CreatePhysicalTable`, which
  `BatchCreatePhysicalTables` and the controlplane lanes all route through) now
  pre-checks the drop-pending tombstone: a marked orphan is drop-finished (the
  same authorization the delete reconcile uses) BEFORE the idempotent create, so
  install/apply can no longer bind new metadata to a deleted object's table. A
  markerless pre-existing table (legitimate resume, or foreign) is untouched —
  resume adoption unchanged, foreign tables never dropped. Greenfield bootstrap
  (no `_System_Table` yet → probe 1146) proceeds; other probe errors fail closed
  (retryable, and the lanes are resume-safe by contract). Residual: a markerless
  crash orphan (crash between metadata commit and marker write) is adoptable by
  install lanes exactly as base strict-create residue was — operator cleanup per
  the 409 remediation; install-provenance tagging in `_System_Table.CreatedBy`
  remains the named follow-up for that last sliver.

## MERGE-GATE — lockstep ordering (REQUIRED)

The core PR (**NexusCRM #3486**) changes Conflict semantics: a Conflict can now
mean "orphan-blocked create" (no live object), not only "already exists".

`nexuscrm-app-packs` `ToolingCustomObjectCreateNeutralHandler`
(`dialects/dialect-sf-crm/metadata_authoring.go`) historically laundered any
`MetadataAuthoringErrorConflict` into `200 OK`. After #3486 that would report a
false deploy success with no live object.

**Status: SATISFIED AND MECHANICALLY ENFORCED.** The lockstep app-packs change
(re-verify `GetEffectiveSchemaForAuthoring` on Conflict; non-nil -> 200, nil ->
surface the error) merged FIRST as `nexuscrm-app-packs#320`
(`bc7801f6bf9ed8acc423e691a08305bea2dcf3ac`), and this PR pins that revision in
`backend/distributions/appsource/source.go` (`OfficialAppPacksSourceRevision`),
putting the fixed handler in the SAME release unit as the semantics change — a
build of this core revision always carries it. It was a behavioral no-op until
#3486 lands (a Conflict previously always implied live metadata, so the
re-verify returned non-nil), which is why merging it first was safe. Tests:
`TestToolingCustomObjectCreate_ConflictWithoutLiveObjectIsNotSuccess` /
`...WithLiveObjectStaysIdempotent`.