Model and Protect Real Data
Move from temporary demo state to a managed database without losing track of ownership, migrations, or recovery.
By the end
A constrained relational schema, seed and migration strategy, development-to-production boundary, and tested restore procedure.
Model the nouns and rules
Begin with the durable nouns in the workflow: users, requests, assignments, approvals, comments, and events. Give each record a stable identifier and timestamps. Use relationships instead of copying the same fact into many places. If a request has an owner, store a relationship to the user record rather than repeatedly storing the owner's display name as truth.
Turn business invariants into database constraints where appropriate: required fields cannot be null, identifiers are unique, relationships point to real records, and limited values use a constrained type or validation. Application validation gives friendly feedback; database constraints protect truth if another code path makes a mistake.
Separate current state from history
The request table can hold the status needed for efficient display, while a transition table records every change. Store actor, previous state, next state, reason, and timestamp. This makes the current interface fast and preserves the evidence needed to explain how it got there.
Decide deletion policy explicitly. Hard deletion removes a record; soft deletion marks it unavailable; archival moves it out of active work; anonymization removes personal fields while preserving aggregate or operational history. The right choice depends on user expectations, regulation, recovery needs, and cost. Do not let a Delete button silently choose policy.
Use migrations as change history
A schema migration is a repeatable description of how the database structure changes. It belongs with the code so development, tests, and production can reach compatible states. Ask Agent to generate and explain migrations, inspect destructive operations, and keep schema changes in the same reviewed slice as the code that uses them.
For a risky change, use expand-and-contract: add a compatible new field, write both old and new forms if necessary, backfill existing records, switch readers, verify, then remove the old field later. This costs more steps but avoids turning one deployment into an irreversible deadline.
Respect environment separation
Development data is for building and testing; production data belongs to real users and the published app. Replit's current database workflow distinguishes development from production during publishing. Never assume that seeing a development row proves a production write, or that rolling back project code automatically restores production data.
Use synthetic seed data for repeatable tests. Avoid copying sensitive production data into development. If realistic structure matters, generate safe examples with the same shapes and edge cases. Record which environment a database query inspected in every receipt.
Practice backup and restoration
A recovery feature you have never tested is a hope. Before the app matters, create a reversible development exercise: record known rows, make a checkpoint, change data, restore the database option deliberately, and verify the known state. Understand that checkpoint rollback treats database restoration as a separate choice and does not automatically restore production data.
For production, document the current platform-supported backup or point-in-time recovery path, who may invoke it, what data loss window is acceptable, and how success will be verified. Read the live Replit documentation before an incident because database capabilities and interfaces can evolve.
Working reference
Commands and patterns
Check only that a database URL exists
node -e "console.log({DATABASE_URL_present: Boolean(process.env.DATABASE_URL)})"Confirm configuration presence without printing the connection string. A credential must never appear in logs or guide receipts.
Inspect schema migrations
find . -maxdepth 4 -type f \( -iname '*migration*' -o -path '*/migrations/*' \) | sort | head -80
Locate migration artifacts for review; the exact directory and migration command depend on the generated stack.
Safe aggregate SQL receipt
SELECT status, COUNT(*) AS records FROM requests GROUP BY status ORDER BY status;
Run a non-sensitive aggregate in the built-in SQL runner to verify expected states without exporting personal fields.
When the happy path breaks
Failure modes
Symptom
Records disappear after the server restarts or a new release ships.
Likely cause
The app stored important state in memory, a local file, or browser storage instead of the managed database.
Recovery
Identify persistence requirements, add the database through Agent, migrate the workflow, and verify after restart and reload.
Symptom
A rollback restores code but leaves data in an incompatible state.
Likely cause
Code and database recovery were assumed to be one automatic operation.
Recovery
Review checkpoint database options, use backward-compatible migrations, and maintain an explicit production data recovery runbook.
Symptom
A schema change works on empty development data but fails with existing records.
Likely cause
The migration added a required or transformed field without a default, backfill, or staged transition.
Recovery
Test against representative synthetic history and use an expand-backfill-switch-contract sequence.
Hands-on lab
Make the workflow persistent
Add managed SQL persistence and attributable state history to the request-and-approval app.
- 01Define request, user reference, assignment, approval, and transition records with keys and constraints.
- 02Ask Agent to integrate Replit Database and explain the schema and generated migration before applying it.
- 03Seed synthetic records covering empty, active, approved, rejected, long-text, and missing-optional-field cases.
- 04Create, reload, restart, and query a record to prove persistence and correct relationships.
- 05Create a development checkpoint, change known data, perform a deliberate database-inclusive restore, and verify the expected rows.
- 06Write the separate production recovery procedure without attempting a production restore.
Deliverable: Reviewed schema and migrations, synthetic seed data, persistence receipts, a development restore receipt, and a production recovery runbook.
Reusable artifact
Data contract and recovery card
Keep one card for each consequential record type.
RECORD: [singular name and purpose] OWNER / SCOPE: [who may access which rows] IDENTIFIER: [stable key] FIELDS: [name, type, required, source, sensitivity] RELATIONSHIPS: [record -> relationship -> record] CONSTRAINTS: [unique, foreign key, allowed values, invariants] HISTORY: [events recorded and retention] DELETION POLICY: [hard, soft, archive, anonymize] MIGRATION PLAN: [expand, backfill, switch, contract] DEVELOPMENT RESTORE: [checkpoint procedure and verified date] PRODUCTION RECOVERY: [owner, method, data-loss window, validation]
Before you move on
Receipt checklist
- A record created through the app remains correct after page reload and runtime restart.
- The database rejects at least one invalid relationship or invariant independently of the interface.
- Every status change creates attributable transition history without overwriting earlier events.
- A development database restore returns known synthetic rows to their checkpoint state.
- Every query or screenshot labels development or production explicitly.
Primary sources
