eduKate Singapore

VIEW THIS AS

Auto mode follows the Route Engine until you choose a viewpoint.

YOU ARE HERE

ROUTE CHECK

CONNECTED TO

WHAT NEXT

Use the canonical route for this room, or HELP if you are unsure.

Next useful routeCanonical hubHELP

How Superintelligence Works | Agent Database Migrations — Expand–Contract, Backfills and Safe Rollbacks

—

by

eduKate Asia

Did you know? A correct SQL migration can still break the old application that is serving parents and students. Database safety needs staged compatibility, verified backfill and explicit permission before cleanup.

Quick answer: expand schema compatibly, migrate application behavior, backfill in measured batches, compare old and new results, then contract only after every dependency and rollback limit is understood.

This educational chapter continues How Super Intelligence Works. All invented school-record counts are teaching examples, not results from any eduKate system.

Contents and reading route

1. The short answer: schema changes are coordinated releases

Did you know? A database migration can be syntactically correct and still break a working application. An agent might add a new column that current code does not yet understand, remove a field still used by an older worker, or run a giant backfill that consumes the capacity needed for live parent and student queries. The visible SQL statement is only one part of the change. The real problem includes simultaneous application versions, existing data, locking, recovery and authorization.

A safer design treats a production schema change as a staged release rather than a one-line edit. Expand the schema while preserving old readers and writers, introduce compatible application behavior, migrate existing values in controlled batches, verify the result, switch to the new contract, and only then consider removing legacy structures. Each phase needs evidence and a possible stop point. An agent’s proposal should be reviewable by someone who understands both the stored data and the service using it.

The practical rule is “compatibility before cleanup.” Preserve the immutable existing source of truth while new structures are introduced, avoid large unbounded writes, enforce an approved migration plan and verify what the database actually accepted. This chapter uses fictional school-record examples rather than claims about eduKate’s own schema or any deployed agents. Its title does not assert that artificial superintelligence already exists.

2. Why a table definition is also an application interface

A database schema is a contract between stored rows and the software reading or changing them. Column names, types, nullability, defaults, uniqueness, foreign keys, indexes and privileges can all affect application behavior. A migration that passes a parser may still violate an assumption in an old application binary, dashboard report or background worker. Schema compatibility is therefore not just a property of the database engine.

Imagine a school attendance table with a Boolean field called is_present. Developers decide to represent attendance as a richer status with values such as present, absent, excused and pending. Replacing the Boolean column immediately could break old code that selects it, old forms that write it, and reports expecting exactly true or false. Some historical records cannot be converted to a richer meaning without human context.

The correct first question is not “How do I drop is_present?” It is “Which readers and writers still rely on the old field, what does the new field mean, and can both representations coexist temporarily without corrupting records?” An AI agent should map these dependencies before drafting commands. If the map is incomplete, its confidence should go down, not its willingness to delete.

3. Expand, migrate and contract in plain language

The expand–contract pattern separates a potentially breaking replacement into safer stages. During expand, introduce the new schema in a way that current software can continue to run. During migrate, change application logic and backfill existing rows while maintaining compatibility. During contract, remove legacy structures only after every dependent reader and writer has demonstrably moved away. The principle is widely used because deployed software does not change instantaneously.

For the attendance example, add an optional attendance_status field without removing is_present. Deploy readers that can handle both during the transition. Decide how old Boolean values map to the new vocabulary and how new submissions update both representations where necessary. Migrate historical records with a documented mapping and a review path for ambiguous cases. Eventually make the new status authoritative and retire the old column after verifying that no supported code still needs it.

The official Prisma expand-and-contract guide offers a concrete example of replacing a Boolean with an enum through staged changes. Its exact implementation is specific to Prisma and its documented database context; the general lesson is broader. Avoid taking a framework example as proof that all PostgreSQL versions, migration tools or hosted databases have identical locking and transaction behavior.

4. Compatibility has four important combinations

At least four combinations matter during a rolling deployment: old application with old schema, old application with expanded schema, new application with expanded schema, and new application with contracted schema. Teams often test only the last combination because it matches the hoped-for final state. But most production incidents happen while the service is between versions or a worker has not yet restarted.

Suppose half the workers have been updated and half have not. New workers begin writing attendance_status while old workers still write is_present. If neither side maintains the compatibility policy, the two columns can disagree. A new reader that blindly trusts one column could show absent while the old view shows present. Designing a deterministic source-of-truth and transition policy prevents this inconsistency from becoming a human-facing error.

Write a compatibility matrix listing which application versions can read and write each schema phase. Identify exactly when a new field becomes required, when writes are switched, and when old readers are no longer supported. A rollout gate should be based on deployment inventory and verified behavior, not an agent’s optimistic assumption that a commit merged into Git has reached every worker.

5. Schema defaults and null values hide tricky semantics

Adding a nullable field is often easier to roll out than making a new field mandatory immediately, but the resulting nulls need interpretation. Does null mean unknown, not migrated, not applicable or deliberately left blank? Those are different business states. A backfill that substitutes absent for every null can transform incomplete historical data into a false factual assertion about a student’s attendance.

Defaults also need careful review. A database default may apply to rows created after the change, while older rows may require transformation depending on the database and migration. A value that is safe for new forms is not necessarily a truthful historical value. The application may also write an explicit null, bypassing the intent behind a default. Never infer a migration’s full data effect from a field declaration alone.

Before populating a new column, define conversion rules in a reviewed specification. For example, true may map to present and false may map to absent only if the original Boolean unequivocally represented those meanings. A special pending state may be necessary for rows lacking trustworthy evidence. Measure unmatched values and make them visible to human reviewers. The algorithm cannot turn ambiguity into fact simply by filling an empty cell.

6. Backfills are data operations, not decorations

A backfill populates or transforms historical records after a schema is introduced. For a small table, a transaction may finish quickly; for millions of records, a single unbounded update can create lock contention, large write amplification, replication lag or lengthy rollback. The migration plan must account for capacity and the service’s normal read and write traffic. Fast in staging does not guarantee safe in production.

Chunk work by a stable key or another supported cursor, record durable progress and verify each batch before advancing. A batch should have clear selection criteria, an idempotent transformation and a way to avoid overwriting values that have already been changed by active users. Avoid relying exclusively on OFFSET pagination over a changing dataset, because inserts and deletes can shift rows between pages.

Suppose a fictional system holds 12,000 records. A first supervised batch converts 500 rows and verifies 500, then a second batch processes another 500. That is evidence for 1,000 transformed records, not a promise that the remaining 11,000 share the same quality. Monitor throughput, failures, lock waits and exceptional values as work continues. Slow down or pause when the backfill harms critical live operations.

7. A reproducible conversion ledger with 12,000 records

Consider the following invented attendance conversion audit. The table contains 12,000 legacy rows: 8,300 unambiguous present values, 2,600 unambiguous absent values, 700 rows that represent not-applicable entries, and 400 rows whose source meaning cannot be verified. The total is 8,300 + 2,600 + 700 + 400 = 12,000. A reliable migration preserves those four categories rather than forcing every record into a binary outcome.

Suppose only the first two groups can be automatically mapped to present and absent. That gives 10,900 safely converted rows, or approximately 90.83 percent of the total. The remaining 1,100 require an explicit not-applicable representation or review. Advertising 100 percent completion because the SQL statement reached every row would be a misuse of the completion metric.

The success condition is more than row count. Verify the new representation against the source fields and business rules, confirm that new writes remain consistent during the conversion, and audit a sample of transformed records. The 400 ambiguous rows must stay marked as uncertain or be reviewed with appropriate context. An AI agent should generate the discrepancy report, not silently invent a label to improve a percentage.

8. Dual writing needs an owner for conflicts

During expansion, some designs temporarily update both old and new columns. Dual writing can preserve compatibility for old and new readers, but it creates new failure modes if the two writes are not atomic or if the transformation is not reversible. A status enum may express distinctions that cannot fit into a Boolean. Writing ‘excused’ as false might allow old reports to group excused attendance with unexplained absence.

A safer transition explicitly defines a canonical representation, compatibility mappings and the behavior for values the old schema cannot express. Whenever possible, use one transaction for changes to the same database row and validate consistency. If old and new representations are held in separate systems, a transactional outbox, reconciliation process or staged source-of-truth handoff may be needed; a naive pair of API calls is vulnerable to partial failure.

The agent’s job is to show which combinations remain compatible, which are lossy and which require human decisions. It should not quietly convert richer values into an old approximation for operational convenience. Keep compatibility fields only as long as required; prolonged dual writing increases complexity and the risk that different consumers disagree about the authoritative answer.

9. Read transitions are as important as write transitions

An application may be able to write the new column while still reading the old one for weeks. This can conceal errors until the final read switch. A safe migration gradually proves that the new representation supports correct searches, filters, reports and access checks before it becomes the only source. Shadow reads can compare old and new results without exposing new behavior to users until discrepancies are understood.

Suppose a dashboard reports 1,400 present students through its old Boolean query but 1,396 through the new status logic. Rather than selecting whichever figure looks convenient, investigate the four-record difference. It could come from pending statuses, stale backfill progress, a query bug or an inconsistent mapping. The mismatch is a useful signal before the new read path is promoted.

Do not use shadow comparisons as a license to copy sensitive records into uncontrolled logs. Aggregate discrepancies and store minimal identifiers accessible only to authorized reviewers. Where accuracy matters, maintain an auditable trail from the source revision to the current derived value so that a later review can explain why a record changed.

10. Database locks are hidden costs of migration

Changing a schema can acquire locks even when a command looks simple. The exact lock level, table rewrite behavior and duration depend on the database engine, version and command. An AI model that tells an operator “adding one column is always instantaneous” is making an unsafe generalization. Some operations are metadata-only in particular systems, while others require touching many rows or coordinating with running transactions.

Before execution, inspect the database version, table size, transaction activity and documentation for the specific DDL operation. Evaluate expected lock waits, replication effects and peak traffic. Define a statement timeout or safe abort plan when appropriate. If the migration needs an exclusive period, schedule it with the relevant owner rather than attempting it opportunistically because the site seems quiet.

The agent should produce an operator-readable risk table: operation, expected lock behavior, affected tables, old/new compatibility, expected duration class, stop signal, verification query and rollback limit. Actual execution data should supersede an estimate. Never claim a live migration was safe just because a generated explanation mentioned locks.

11. PostgreSQL concurrent indexes are not a universal cure

PostgreSQL supports CREATE INDEX CONCURRENTLY to build an index while permitting ordinary writes, as explained in its CREATE INDEX reference. That is useful for production systems, but it performs more work and has special restrictions: for example, the command cannot run inside a standard transaction block, and a failed concurrent build can leave an invalid index requiring explicit inspection and repair.

Even a concurrent index build can consume CPU, I/O and resources, or wait for older transactions. It is not free. A unique concurrent index has additional caveats about when uniqueness enforcement begins and how failures are handled. A migration wrapper that indiscriminately puts every statement inside one transaction can therefore fail when it meets a command that forbids that context.

Before using concurrent index creation, verify the database version and target table, review uniqueness constraints, monitor build progress and confirm the resulting index is valid and usable. If a command fails, inspect the database catalog instead of blindly retrying another creation with a new name. The presence of an index object is not sufficient proof that the index finished successfully.

12. Constraints can be introduced in phases

PostgreSQL supports adding certain check and foreign-key constraints as NOT VALID and validating them later. Under the documented semantics, new or updated rows can be subject to the new check while pre-existing rows are validated separately. This can reduce disruptive scanning at the moment a constraint is installed. However, the exact behavior, lock modes and supported constraint types are specific to the platform.

An example is a new rule that status must come from an approved set. If older rows contain invalid values, immediate full validation may fail. A staged rollout can enforce the desired rule for new modifications, identify historical exceptions, repair them under approved mapping rules, and then validate the constraint. The PostgreSQL ALTER TABLE documentation describes VALIDATE CONSTRAINT and its concurrency implications.

Do not turn constraint validation into a silent data-cleaning license. A constraint failure may reveal meaningful old records that require business review. The proper response is to identify the violating set, classify causes and decide how to preserve semantics. Passing a constraint by replacing every unrecognized status with ‘absent’ could produce technically valid but factually wrong attendance records.

13. Foreign keys and uniqueness need historical inspection

Adding a foreign key assumes relationships actually exist. A tutoring invoice may refer to a class that was deleted or migrated under an older identifier; declaring a new relationship without reconciling orphan records can fail or pressure developers to erase history. Likewise, a unique index on an email field may encounter duplicate addresses representing legitimate shared family contacts rather than duplicate people.

A preflight should count orphan references, null keys and duplicates under the exact comparison and collation rules of the chosen database. Distinguish genuinely erroneous records from valid cases the new model must support. A household may have multiple children sharing a parent email; a unique constraint on email across children would encode the wrong business rule. The database should enforce real invariants, not a convenient but inaccurate simplification.

After cleanup, test representative inserts, updates and deletes against the new constraint in a staging environment. Verify cascade or restriction behavior. A migration agent should not choose ON DELETE CASCADE casually on records involving students or financial history; cascading deletion can have serious consequences. Prefer explicit authorization and reviewed retention requirements before introducing destructive referential actions.

14. Rollback can be impossible after data is destroyed

A schema migration may be reversible in its first stage and effectively irreversible later. Removing an unused new column before adoption can be straightforward. Dropping an old column that held data no longer represented elsewhere is different. Rolling back application code will not reconstruct values that were discarded by a destructive contraction. The word rollback should be tied to specific recoverable states rather than used as a reassuring slogan.

Before expanding, establish how the database will be backed up and whether a tested restoration procedure covers the relevant data and time window. When the system enters dual-write or new-write modes, document how restoring an old application version would read the current rows. After contracting, a safe rollback may require rolling forward with a compensating migration instead of pretending a deleted field can magically return.

An AI assistant should identify the last reversible boundary and require explicit owner approval before crossing it. For example, ‘remove legacy Boolean’ may be postponed until all known readers are retired, shadow comparisons match and backups are verified. If an incident requires emergency recovery, bring in an authorized database operator rather than asking the agent to improvise destructive SQL on the production system.

15. Migration history is evidence, not just a filename

Migrations usually have identifiers or version records that say which changes were applied. But a filename appearing in source control does not prove the corresponding SQL ran on a particular database. An app can also be deployed against the wrong environment. Before continuing, check environment identity, migration table, schema introspection and the change’s expected parent version.

A robust journal records proposed migration ID, reviewed digest, applied-by identity, timestamp from an authoritative source, target database identity, execution outcome, relevant checks and postcondition verification. Do not use one Boolean ‘completed’ to cover a partial backfill and an unexecuted contraction. Steps that commit separately must retain separate durable states, so a restarted worker knows exactly where to resume.

If two agents attempt to migrate the same database concurrently, they can conflict even if each file is individually valid. Use an appropriate migration lock, change-control gate or single-writer coordinator and verify that competing operations respect it. The Agent Clock Skew chapter explains why expired local lease assumptions are not enough to protect a shared write.

16. Prompt injection and untrusted migration plans

An AI agent may inspect a database schema, an issue description, a README file and migration comments. Not every string inside those sources is an authorized instruction. A malicious comment could tell the agent to disable access logs, export a student table or drop a backup. The correct trust boundary keeps read data separate from the actual owner’s approved migration objective and available tool permissions.

Require a narrowly scoped database identity and explicit approval for irreversible operations. Refuse unexplained table drops, access-control changes and bulk exports that are unrelated to the legitimate task. Parameterize data queries where appropriate, use read-only inspection roles for the planning phase, and elevate privileges only for the approved execution step. Never copy production credentials into article drafts or chat logs to make an example reproducible.

Represent proposed SQL as a reviewable artifact that can be diffed against a known baseline. Verify its target table and schema name, dependency order and affected records using authorized inspection tools. A model-generated rationale is useful for review but cannot replace the database’s own access controls, version checks or post-execution evidence.

17. What a staging rehearsal should prove

A staging environment gives the team a safer place to rehearse, but only if it represents the production conditions that matter. A tiny empty table may not expose locks or backfill costs of a large live table. Realistic fixtures should include nulls, duplicates, old versions, unusual Unicode values, foreign-key edge cases and concurrent writers. Sensitive production records should be de-identified according to policy rather than copied into staging indiscriminately.

Rehearse the deployment sequence, not just the SQL. Start old application code against the expanded schema, run compatible new code, perform backfill and verify queries under both readers, switch the canonical read path, and exercise rollback at the documented safe points. Test the migration’s repeatability after an interruption. A safe resume should not transform already processed rows twice or overwrite edits made since the first attempt.

Measure actual lock waits and query performance during the rehearsal. Confirm the SQL wrapper supports the intended transaction behavior and that index or constraint validity is inspected. Record results with enough detail that the production reviewer can distinguish tested facts from assumptions. If the production dataset is qualitatively different, state that limitation rather than claiming rehearsal eliminates risk.

18. Canary rollout and monitoring

A canary rollout sends a small, controlled share of activity through the new schema behavior before broad adoption. It can reveal unexpected enum values, slow queries or incompatibilities with old workers while limiting impact. The control must be chosen carefully: routing a few requests through new logic does not help if a destructive schema command already affected every tenant.

Compare error rates, request latency, database CPU, lock waits, replication lag, new/old field consistency and business outcome checks before widening the rollout. For a school attendance service, compare accepted submission counts and parent-visible status accuracy, not just query response times. An application can return HTTP 200 while storing or displaying the wrong meaning.

Define clear rollback or pause thresholds in advance. A canary that produces inconsistent records should stop before the mismatches become hard to isolate. When alerting, preserve the affected source version and safely minimal identifiers. The final decision to expand beyond the canary belongs to the authorized release owner, not to a language model estimating that things ‘look good’.

19. Partial migration failure and checkpoint recovery

Suppose a backfill runs in twenty-five batches. It completes sixteen and fails midway through batch seventeen. A journal with only an overall percentage cannot establish which rows committed in the failed batch. A better design stores a checkpoint after each verified batch and uses idempotent selection so any ambiguous batch can be inspected before it is retried. The state is ‘sixteen verified, one uncertain’, not ‘seventeen done’.

If each completed batch contained 400 unique rows, the verified count is sixteen multiplied by 400, or 6,400 rows. The remaining rows require accounting against the original expected population. The failed batch might have committed partially, completely or not at all depending on transaction boundaries. Re-query the affected key range, compare source versions, and resume without overwriting newer user changes.

Some data transformations are non-idempotent, such as appending a suffix or incrementing a total. For migrations, replace these with deterministic mappings or explicit deduplication guards where possible. If the transformation cannot be made safely repeatable, an operator should manage the uncertainty and perform an audited correction. Speed is less important than maintaining the truth of the underlying records.

20. Publishing after a migration is a separate step

An agent may successfully update a database and then prepare a public article describing the result. The existence of a migration log does not prove publication, just as a post-creation response does not prove the schema change was safe. Separate the operational evidence from editorial reporting. A published summary should state the change, its verified scope, any unresolved exceptions and any meaningful effect on users.

For example, if 10,900 of 12,000 fictional records were converted under reviewed mappings and 1,100 remain pending, do not announce ‘attendance migration complete’. The true report is more useful: the new structure is in place, the verified unambiguous records were converted, and exceptions are queued for review. Readers need accurate status more than celebratory language.

When a public page or internal note is produced, verify the publishing system’s returned post ID, content revision and status. A broken network acknowledgement should trigger readback rather than blind duplicate creation. The Agent Batch Writes article discusses this broader distinction between attempted and verified results.

21. A changed-context exercise: learning-level labels

A fictional education service used one field called level to store labels like ‘primary’, ‘secondary’ and ‘junior college’. It now needs separate stage and year values. The legacy dataset also contains free-text entries like ‘Sec 1’, ‘Secondary One’ and ‘S1’. An agent is asked to propose a migration that helps parents filter courses without silently inventing a year for ambiguous records.

Begin by defining the new vocabulary and mapping each supported legacy value to a reviewed meaning. Add optional stage and year fields, preserve the original level, and run counts of recognized and unrecognized formats. Backfill only deterministic conversions automatically. Mark ambiguous values for review rather than forcing them into a plausible year. Test school-year boundaries and any real program distinctions before the new filter is launched.

Next test the old website code against the expanded table, then the new code with shadow comparisons. Once the new filters show the correct courses, make the new fields authoritative and monitor usage. Remove the old level field only after all remaining dependent pages, reports and integrations have been checked. This is an example of migration as an educational data-quality project, not a race to eliminate an inconvenient old column.

22. Checklist exercise: can this change ship?

Question one: a migration script passes locally. Is production safety proved? No; application compatibility, data distribution, locks and authority still need review. Question two: a new field is nullable but every historical null is automatically replaced with ‘absent’. Is that necessarily correct? No; unknown and absent differ. Question three: does CREATE INDEX CONCURRENTLY avoid all resource consumption? No; it reduces some blocking behavior but requires work and can fail.

Question four: sixteen of twenty-five backfill batches were verified; may the agent report the entire migration complete? No. Question five: an old worker still writes the legacy field. Is it safe to drop that field because the new deployment passed health checks? No; verify all compatible writers have retired. Question six: can rolling back application code restore a destroyed column’s historical data? Not without a valid preservation or restore strategy.

More advanced review: describe which metrics would stop the release, which versioned records would establish a safe backfill checkpoint, what evidence authorizes the contract phase, and which changes remain reversible. If a proposal cannot answer those questions, it needs clarification before production execution. A careful ‘not yet’ is often the most valuable result an automated reviewer can provide.

23. Frequently asked questions

Is expand–contract always necessary? Not for every schema change, but it is a valuable pattern when applications remain live during a potentially breaking change. The exact plan depends on database and deployment behavior.

Can an AI agent safely write SQL? It can draft, explain and help review SQL. Production execution needs narrowly scoped credentials, approved changes, database-specific checks and post-execution verification.

Are backfills just one UPDATE command? Small tasks may be simple, but large or concurrent datasets often need bounded batches, stable keys, checkpointing, validation and operator controls.

Does a new default repair all old records? Do not assume so. Defaults and historical transformation have different semantics across systems; verify actual rows and intended business meaning.

Does CREATE INDEX CONCURRENTLY mean no downtime risk? No. It has resource costs, restrictions and possible invalid-index failure states documented by PostgreSQL.

When can an old column be removed? After all supported readers and writers have migrated, correctness checks pass, and the owner accepts the rollback and retention implications.

What if the migration partially commits? Inspect durable checkpoints and actual database state, reconcile unknown batches and resume idempotently when the transformation permits it.

Why should parents care about database migration? Because a technically clean schema is not useful if it changes the meaning of attendance, learning levels or approved school information.

24. Sources and reader pathways

The Prisma guide to expand–contract migration presents a concrete staged replacement of a Boolean field. For PostgreSQL-specific online-change considerations, consult the official CREATE INDEX and ALTER TABLE documentation. Exact lock and transactional behavior must be checked for the deployed engine and version.

Related eduKateSG chapters include Transactional Outbox, Batch Writes and Git Merge Safety. The complete reading route begins at How Super Intelligence Works.

The closing lesson: a database schema is a living contract, not merely a collection of column names. Expand compatibility before removing anything, migrate data with verifiable meaning, preserve recovery options, and let the authoritative database and service receipts establish what really changed.

Discover more from eduKate Singapore

Subscribe now to keep reading and get access to the full archive.

Continue reading