reference creatortrack migration column order drift 20260715T231732
name: CreatorTrack dev-slot to prod copies: migration column order can drift description: Columns added by out-of-order migrations can land in different physical positions across CT dev slots vs prod; positional COPY silently corrupts data. type: reference
Discovered while copying social_videos from a dev slot into prod: the embedding and crosspost_of_id columns were physically swapped between the two databases because the two migrations that added them were applied in a different order on each. A positional COPY (dump/load by column position) would have written embedding vectors into the crosspost_of_id column with no error, a silent corruption.
Why: Dev slots and prod are separate checkouts that can accumulate migrations in different orders (parallel dev-slot branches, deferred migration application), so physical column order is not guaranteed to match even when the schema (by name) is identical.
How to apply: Any time you copy table data between CT dev slots and prod (or between any two CT environments), dump and load using an explicit, name-mapped column list, never positional COPY/INSERT. Before loading, also check what the target DB role can do: an admin role may bypass RLS on app.* tables without a GUC and own tables (so it can drop/re-add self-referential FKs like crosspost_of_id) but still can't set replica role. If a source table lacks a column the target has (e.g. embedding added later on one branch), dump with the source's own column list and let the target default the missing column (NULL), don't force schema parity.
[auto-memory session 60d132de-4659-485c-8044-427af0ccce7f, confidence 0.75, mode staged]