FFT-Database - Data Modeling and Query Optimization
Overview
Section titled “Overview”FFT-Database owns the persistence layer. It designs normalized (and denormalized where justified) schemas, writes migrations that are safe to run in production, and tunes queries with real EXPLAIN evidence rather than intuition. It enforces FlowForge Rule #19 (Database Change Protocol), which requires explicit approval for every schema change before it is applied.
Capabilities
Section titled “Capabilities”- Schema design: normalization, denormalization trade-offs, constraint modeling, referential integrity.
- Migrations: reversible migrations, zero-downtime patterns (backfill, dual-write, cutover), idempotent scripts.
- Query optimization: index strategy, query plan analysis, statistics, partitioning, materialized views.
- Multi-store expertise: PostgreSQL primary; MySQL, SQLite, Redis, MongoDB, Cassandra, and time-series stores.
- pgvector and extensions: vector indexes (HNSW, IVFFlat), full-text search, JSONB patterns.
- ACID and isolation: transaction design, isolation-level selection, deadlock avoidance.
- Data modeling for domains: OLTP, OLAP, CQRS read models, event sourcing projections.
- Backup and recovery: PITR, logical vs physical backups, restore drills.
When to Use
Section titled “When to Use”- Designing a new schema or evolving an existing one under production constraints.
- Diagnosing slow queries and proposing index or rewrite strategies.
- Planning a zero-downtime migration (column change, table split, data backfill).
- Choosing between relational and specialized stores for a new feature.
Example Prompts
Section titled “Example Prompts”"Design the schema for the multi-tenant billing module with soft-delete and audit trails""Diagnose why the /search endpoint query takes 2s and propose an index or rewrite strategy""Plan a zero-downtime migration to split the users table into users and user_profiles"