OctiAI

Categories

Normalized DB Schema & Safe Migration Template

Convert ORM classes, example SQL, or ad-hoc queries into production-ready DDL, indexes, idempotent migrations, backfills, and monitoring guidance.

What You'll Get

How to Use

Best input to get production-safe output

Include representative sample rows, the top 10 production queries for the affected tables (with explain plans if available), and approximate throughput/read-write ratios. That lets the template recommend precise index prefixes, partial indexes, batching sizes for backfills, and realistic monitoring thresholds.

Works Best With

Best inputs to get precise, production-ready DB schema + migration packages

Prompt Template

# Developer Productivity Prompt Blueprint (use EXACT section order)

## Goal
- Produce a normalized, production-ready database schema spec (DDL) from model classes/ORM mappings/example queries/ad‑hoc SQL, with recommended indexes, constraints, migration scripts (up/down), and a short performance impact note plus suggested monitoring queries. Deliver a minimal-risk migration plan that preserves behavior and data.

## Inputs
- Required inputs (provide at least one): model classes or ORM mappings, example queries, ad-hoc SQL, or existing schema DDL.
- Helpful but optional: sample rows, cardinality estimates, current traffic/throughput, read/write ratio, retention policy, naming conventions, and any backward-compatibility constraints.
- If any required input is missing, the copy-paste prompt below must ask for the minimal missing artifacts before generating the schema.

## Prompt
Use this exact prompt (copy-paste) into the target LLM (ChatGPT/Claude/Gemini):

You are an engineering-grade database designer and migration engineer. Given the following inputs, produce a fully actionable schema and migration package. Inputs:
- SQL dialect: {{sql_dialect}}
- Preferred migration tool/format: {{migration_tool}}
- Model definitions / ORM mappings / example queries / ad-hoc SQL: {{model_definitions}}
- Scale assumptions and constraints (throughput, data volume, retention, RPO/RTO): {{scale_assumptions}}

Tasks (deliver all items below in the Output Format):
1. List explicit Assumptions and Unknowns you infer from the inputs; for each Unknown, specify the minimal artifact or value you need to proceed and a conservative default used if not provided.
2. Produce a normalized logical model (explain chosen normal form) and concrete physical DDL (CREATE TABLE statements) for {{sql_dialect}} including:
   - Column types, NOT NULLs, default values
   - Primary keys and natural vs surrogate key rationale
   - Unique constraints, foreign keys, and ON DELETE / ON UPDATE behaviors
   - Check constraints and enum representations
3. Recommend indexes (including covering and partial indexes where supported), explain index choice per query pattern, and estimate maintenance cost (writes, storage).
4. Provide constraints and integrity checks (application vs DB level) and data migration/backfill strategy for non-nullable columns.
5. Generate an idempotent migration "up" and rollback "down" in the requested {{migration_tool}} format, with explicit steps to:
   - Create new tables/columns safely
   - Backfill data with batching and verification
   - Swap over reads/writes (blue-green or feature-flag approach if needed)
   - Deprecate old columns/tables only after verification
6. Produce patch guidance: minimal SQL diffs and sample ORM model changes (when applicable) focused on minimal surface changes to application code.
7. Provide verification and test checklist: schema validation queries, sample data assertions, end-to-end checks, and a small suite of acceptance queries that prove behavioral parity.
8. Deliver a short "Performance impact" note: expected wins/costs, blocking risks, and monitoring queries (e.g., slow queries, index usage, table size growth) and alerts to add (with thresholds).
9. Enumerate rollback triggers and an explicit rollback plan with verification steps.

Engineering requirements for your reply:
- Prefer minimal diffs; avoid scope creep and do not assume unseen files or behaviors.
- Highlight any destructive operations and provide non-destructive alternatives.
- When multiple valid designs exist, rank options and provide trade-offs (complexity vs performance vs storage).
- Format the deliverable exactly in the Output Format specified below.

If any input is missing or ambiguous, stop and list the minimal missing artifacts using the same naming convention as the Inputs block. Wait for those before producing DDL/migrations.

## Output Format
Output:
  1) Assumptions/Unknowns — explicit list, defaults used, and minimal artifacts required to remove each unknown.
  2) Likely root causes (ranked) — for any current schema/performance problems implied by inputs (e.g., missing index, denormalization causing anomalies).
  3) Fix plan (stepwise) — numbered migration roadmap with exact sequencing, estimated time, and risk level per step.
  4) Patch suggestions — minimal SQL diffs, sample ORM changes, and code rollout instructions.
  5) Test/verification checklist — SQL validation queries, backfill verification queries, performance smoke tests, and acceptance criteria (pass/fail thresholds).

Use clear headings, numbered steps, and concise acceptance criteria. Use SQL snippets for DDL and migration steps. For the migration scripts, include both an "up" and a "down" that are safe to run in production with data migration and backout steps.

## Guardrails
- Do not assume unseen files, services, or undocumented constraints.
- Prefer minimal, incremental migrations; avoid monolithic destructive changes.
- Never DROP production data without an explicit, tested backup and a verified backfill path.
- Explicitly call out any change that may require application deploy coordination (feature flags, dual-write window).
- Provide batching parameters and a safe concurrency model for backfills.
- Flag long-running operations and recommend maintenance windows.
- Include risk checks: data loss, FK cascade surprises, index bloat, replication lag effects, and schema drift; propose mitigations for each.

## Examples
- Mini root-cause + fix excerpt:
  - Root cause (example): High latency on ORDER_ITEMS JOIN caused by missing index on order_items.order_id for queries that filter by order_id and created_at.
  - Minimal fix excerpt:
    - DDL: CREATE INDEX ix_order_items_order_id_created_at ON order_items(order_id, created_at DESC);
    - Migration up (excerpt): add index concurrently or in background; estimate 30 minutes at 100M rows; monitor pg_stat_progress_create_index.
    - Verification query: EXPLAIN ANALYZE SELECT ... WHERE order_id = X ORDER BY created_at DESC; confirm index scan and latency < 200ms.
    - Rollback: DROP INDEX CONCURRENTLY ix_order_items_order_id_created_at (only after traffic test).

Variables

Usage Notes

Not for

Do not include

Normalized DB Schema & Safe Migration Template

Turn model definitions or SQL examples into a normalized logical model and concrete SQL DDL plus safe, idempotent up/down migrations, backfill plans, verification checks, and monitoring queries.

Turn this into a working asset in minutes.

Import into Workspace to auto-fill variables, adapt output style, and iterate with OctiAI's prompt editor.

What you'll get

Output format

Markdown

Best used when

Avoid when

Sign in to save, like, or rate this prompt.

Made for

What to fill in

Use the guidance below to complete each placeholder with the right level of detail.

Sql Dialect

Provide the key details needed for this variable.

Migration Tool

Provide the key details needed for this variable.

Model Definitions

Provide the key details needed for this variable.

Scale Assumptions

Provide the key details needed for this variable.

Rules

Canonical Examples

Input: Postgres + Flyway. Inputs: simplified ORM models for Orders and OrderItems (SQLAlchemy-like), current ad-hoc query patterns: fetch order with items by order_id and list recent orders by user_id ordered by created_at DESC. No explicit indexes beyond PKs. Want normalized DDL, safe migration to add non-null constraints and indexes, and minimal app changes.

Loading result...

Failed to load output.

1) Assumptions/Unknowns — lists minimal missing inputs (sample row counts, retention). 2) Root causes — ranked (missing composite index, nullable columns used in predicates). 3) Fix plan — numbered steps with Flyway SQL up/down. 4) Patch suggestions — small ORM change snippet. 5) Verification checklist — EXPLAIN ANALYZE examples and backfill verification queries.

Why it works: This example mirrors a very common OLTP pattern (orders with many items) where missing composite indexes and non-nullable backfills cause slow joins and occasional null violations. The prompt produces explicit assumptions, concrete CREATE TABLE DDL, index recommendations with concurrent creation, idempotent Flyway-style up/down SQL, and a compact ORM diff — all in a stepwise migration plan with verification queries and rollback triggers.

Input: Postgres + Alembic. Inputs: Django-style models for User, SessionToken, and AuthEvent; sample queries include validating session token, listing active sessions per user, and revoking sessions. Need normalized schema changes to introduce 'refresh_tokens' table, unique constraints, and a revocation tracking mechanism. Scale assumption: ~100M users, high read ratio, heavy concurrent token validation.

Loading result...

Failed to load output.

Assumptions/Unknowns (e.g., token length, TTL), ranked root causes (missing unique index on tokens, no revocation lookup), stepwise Alembic migration up/down, ORM diffs, and explicit test queries (EXPLAIN ANALYZE, token validation latency targets).

Why it works: Auth/session systems require low-latency lookups and strong uniqueness guarantees. This example demonstrates producing a normalized refresh_tokens table, recommended partial/unique indexes for fast token validation and revocation checks, an Alembic migration with idempotent up/down functions, ORM changes (Django model snippets), and a tight verification checklist including atomic rotation/backfill guidance.

Works Best With

Best inputs to get precise, production-ready DB schema + migration packages