Schema Architect
Production-ready database schema design for SQLite + Redis + Neo4j with Rust and Go.
Workflow
Schema design involves these steps:
- Gather requirements (stage, databases, language, domain)
- Select architecture pattern
- Generate schemas per database
- Generate language bindings
- Generate migrations and validation
- Validate output (run
validate_schema.py)
Step 1: Gather Requirements
Determine these before generating anything:
| Question |
Options |
Default |
| Stage |
early (MVP/startup) or advanced (scale/enterprise) |
early |
| Databases |
Any combination of sqlite, redis, neo4j |
All three |
| Language |
rust, go, or both |
Both |
| Domain |
The business domain (e-commerce, SaaS, IoT, etc.) |
Ask user |
If the user provides a use case without specifying these, infer sensible defaults and confirm.
Step 2: Select Architecture Pattern
Early stage — optimized for speed, simplicity, iteration:
SQLite ──── primary store (OLTP, local-first, embedded)
Redis ──── session cache + rate limiting + pub/sub events
Neo4j ──── relationship queries only when graph is justified
Advanced stage — optimized for scale, observability, governance:
SQLite ──── edge/embedded nodes, local write-ahead, sync buffer
Redis ──── distributed cache, streams pipeline, leaderboards
Neo4j ──── knowledge graph, access control graph, recommendation engine
For integrated multi-DB architectures, read references/integration-patterns.md.
Step 3: Generate Schemas
For each selected database, read the corresponding reference and apply its patterns:
- SQLite: Read
references/sqlite.md — normalization, WAL mode, strict typing, indexes
- Redis: Read
references/redis.md — key namespacing, TTL policies, cache-aside pattern
- Neo4j: Read
references/neo4j.md — node labels, relationship types, Cypher constraints
Apply naming conventions from references/naming-conventions.md to ALL generated schemas.
Output per database
| Database |
Files Generated |
| SQLite |
schema.sql, migration files in migrations/ dir |
| Redis |
redis-schema.toml (key namespace + TTL config) |
| Neo4j |
constraints.cypher, schema.cypher |
Use templates from templates/ as starting points — fill in entity-specific content.
Step 4: Generate Language Bindings
For each selected language, read the corresponding reference:
- Rust: Read
references/rust-bindings.md — sqlx/diesel/sea-orm, redis-rs, neo4rs
- Go: Read
references/go-bindings.md — database/sql/gorm, go-redis, neo4j-go-driver
Generate type-safe model structs with proper derives/tags, connection helpers, and repository patterns.
Output per language
| Language |
Files Generated |
| Rust |
models.rs, db.rs (connection pool), cache.rs, graph.rs |
| Go |
models.go, db.go, cache.go, graph.go |
Step 5: Generate Migrations and Validation
Generate versioned migration files following references/migration-patterns.md:
- Timestamped filenames:
YYYYMMDDHHMMSS_description.sql
- Every migration has an
up and down section
- Include audit trail columns:
created_at, updated_at, version
- For advanced stage: add
created_by, deleted_at (soft delete), tenant_id (multi-tenancy)
Step 6: Validate
Run the validation script against all generated files:
python3 /home/ubuntu/skills/schema-architect/scripts/validate_schema.py <output_directory>
The script checks naming conventions, foreign key consistency, index coverage, and migration ordering. Fix any reported issues before delivering.
Enterprise Patterns by Stage
Early Stage Patterns
Apply these for MVPs, prototypes, and startups:
| Pattern |
Implementation |
| Single-tenant SQLite |
One DB file per deployment, WAL mode, STRICT tables |
| Session cache |
Redis strings with session:{user_id} keys, 24h TTL |
| Rate limiting |
Redis sorted sets with sliding window per API key |
| Simple relationships |
SQLite foreign keys first; Neo4j only if graph queries emerge |
| Soft delete |
deleted_at column, never hard-delete user data |
| Optimistic locking |
version INTEGER NOT NULL DEFAULT 1 on mutable tables |
Advanced Stage Patterns
Apply these for production systems at scale:
| Pattern |
Implementation |
| Multi-tenant isolation |
tenant_id on every table, row-level security, Redis key prefix t:{tid}: |
| CQRS |
SQLite for writes, Redis for read-through cache, Neo4j for complex queries |
| Event sourcing |
Redis Streams as event log, SQLite as snapshot store |
| Distributed cache |
Redis Cluster with consistent hashing, cache-aside + write-through |
| Graph access control |
Neo4j (:User)-[:HAS_ROLE]->(:Role)-[:PERMITS]->(:Resource) |
| Schema versioning |
Migration table with checksums, rollback scripts, blue-green deploys |
| Audit trail |
Separate audit_log table with entity_type, entity_id, action, diff_json |
| Connection pooling |
Rust: sqlx::Pool / Go: sql.DB with SetMaxOpenConns, Redis pool per service |
Key Principles
These rules apply to ALL generated schemas regardless of database or stage:
- Normalize to 3NF minimum — denormalize only with measured justification
- Every table gets a primary key — prefer
INTEGER PRIMARY KEY (SQLite) or UUIDs (distributed)
- Every foreign key gets an index — no exceptions
- Timestamps on everything —
created_at and updated_at with UTC, never local time
- Constraints at the DB level — NOT NULL, CHECK, UNIQUE enforced in schema, not just app code
- Redis keys are namespaced —
{service}:{entity}:{id}:{field} pattern always
- Neo4j relationships are verbs —
FOLLOWS, PURCHASED, BELONGS_TO, never nouns
- Migrations are immutable — never edit a deployed migration, always create a new one
- Document everything — inline SQL comments on non-obvious columns, README per schema dir
1---2name: schema-architect3description: >4 Design and generate production-ready database schemas for SQLite, Redis, and Neo4j5 with type-safe Rust and Go bindings. Generates integrated multi-database architectures6 with migrations, validation, caching layers, and enterprise patterns.7 Use when: user asks to design a database schema, create data models, plan migrations,8 set up Redis caching, design a graph database, generate ORM models in Rust or Go,9 or architect a multi-database system. Triggers on: schema design, data modeling,10 database architecture, migration planning, cache strategy, graph modeling.11metadata:12 source: https://skillmd.com/skills/othmanadi/schema-architect13---1415# Schema Architect1617Production-ready database schema design for SQLite + Redis + Neo4j with Rust and Go.1819## Workflow2021Schema design involves these steps:22231. Gather requirements (stage, databases, language, domain)242. Select architecture pattern253. Generate schemas per database264. Generate language bindings275. Generate migrations and validation286. Validate output (run `validate_schema.py`)2930## Step 1: Gather Requirements3132Determine these before generating anything:3334| Question | Options | Default |35|---|---|---|36| **Stage** | `early` (MVP/startup) or `advanced` (scale/enterprise) | `early` |37| **Databases** | Any combination of `sqlite`, `redis`, `neo4j` | All three |38| **Language** | `rust`, `go`, or both | Both |39| **Domain** | The business domain (e-commerce, SaaS, IoT, etc.) | Ask user |4041If the user provides a use case without specifying these, infer sensible defaults and confirm.4243## Step 2: Select Architecture Pattern4445**Early stage** — optimized for speed, simplicity, iteration:4647```48SQLite ──── primary store (OLTP, local-first, embedded)49Redis ──── session cache + rate limiting + pub/sub events50Neo4j ──── relationship queries only when graph is justified51```5253**Advanced stage** — optimized for scale, observability, governance:5455```56SQLite ──── edge/embedded nodes, local write-ahead, sync buffer57Redis ──── distributed cache, streams pipeline, leaderboards58Neo4j ──── knowledge graph, access control graph, recommendation engine59```6061For integrated multi-DB architectures, read `references/integration-patterns.md`.6263## Step 3: Generate Schemas6465For each selected database, read the corresponding reference and apply its patterns:6667- **SQLite**: Read `references/sqlite.md` — normalization, WAL mode, strict typing, indexes68- **Redis**: Read `references/redis.md` — key namespacing, TTL policies, cache-aside pattern69- **Neo4j**: Read `references/neo4j.md` — node labels, relationship types, Cypher constraints7071Apply naming conventions from `references/naming-conventions.md` to ALL generated schemas.7273### Output per database7475| Database | Files Generated |76|---|---|77| SQLite | `schema.sql`, migration files in `migrations/` dir |78| Redis | `redis-schema.toml` (key namespace + TTL config) |79| Neo4j | `constraints.cypher`, `schema.cypher` |8081Use templates from `templates/` as starting points — fill in entity-specific content.8283## Step 4: Generate Language Bindings8485For each selected language, read the corresponding reference:8687- **Rust**: Read `references/rust-bindings.md` — sqlx/diesel/sea-orm, redis-rs, neo4rs88- **Go**: Read `references/go-bindings.md` — database/sql/gorm, go-redis, neo4j-go-driver8990Generate type-safe model structs with proper derives/tags, connection helpers, and repository patterns.9192### Output per language9394| Language | Files Generated |95|---|---|96| Rust | `models.rs`, `db.rs` (connection pool), `cache.rs`, `graph.rs` |97| Go | `models.go`, `db.go`, `cache.go`, `graph.go` |9899## Step 5: Generate Migrations and Validation100101Generate versioned migration files following `references/migration-patterns.md`:102103- Timestamped filenames: `YYYYMMDDHHMMSS_description.sql`104- Every migration has an `up` and `down` section105- Include audit trail columns: `created_at`, `updated_at`, `version`106- For advanced stage: add `created_by`, `deleted_at` (soft delete), `tenant_id` (multi-tenancy)107108## Step 6: Validate109110Run the validation script against all generated files:111112```bash113python3 /home/ubuntu/skills/schema-architect/scripts/validate_schema.py <output_directory>114```115116The script checks naming conventions, foreign key consistency, index coverage, and migration ordering. Fix any reported issues before delivering.117118## Enterprise Patterns by Stage119120### Early Stage Patterns121122Apply these for MVPs, prototypes, and startups:123124| Pattern | Implementation |125|---|---|126| **Single-tenant SQLite** | One DB file per deployment, WAL mode, `STRICT` tables |127| **Session cache** | Redis strings with `session:{user_id}` keys, 24h TTL |128| **Rate limiting** | Redis sorted sets with sliding window per API key |129| **Simple relationships** | SQLite foreign keys first; Neo4j only if graph queries emerge |130| **Soft delete** | `deleted_at` column, never hard-delete user data |131| **Optimistic locking** | `version INTEGER NOT NULL DEFAULT 1` on mutable tables |132133### Advanced Stage Patterns134135Apply these for production systems at scale:136137| Pattern | Implementation |138|---|---|139| **Multi-tenant isolation** | `tenant_id` on every table, row-level security, Redis key prefix `t:{tid}:` |140| **CQRS** | SQLite for writes, Redis for read-through cache, Neo4j for complex queries |141| **Event sourcing** | Redis Streams as event log, SQLite as snapshot store |142| **Distributed cache** | Redis Cluster with consistent hashing, cache-aside + write-through |143| **Graph access control** | Neo4j `(:User)-[:HAS_ROLE]->(:Role)-[:PERMITS]->(:Resource)` |144| **Schema versioning** | Migration table with checksums, rollback scripts, blue-green deploys |145| **Audit trail** | Separate `audit_log` table with `entity_type`, `entity_id`, `action`, `diff_json` |146| **Connection pooling** | Rust: `sqlx::Pool` / Go: `sql.DB` with `SetMaxOpenConns`, Redis pool per service |147148## Key Principles149150These rules apply to ALL generated schemas regardless of database or stage:1511521. **Normalize to 3NF minimum** — denormalize only with measured justification1532. **Every table gets a primary key** — prefer `INTEGER PRIMARY KEY` (SQLite) or UUIDs (distributed)1543. **Every foreign key gets an index** — no exceptions1554. **Timestamps on everything** — `created_at` and `updated_at` with UTC, never local time1565. **Constraints at the DB level** — NOT NULL, CHECK, UNIQUE enforced in schema, not just app code1576. **Redis keys are namespaced** — `{service}:{entity}:{id}:{field}` pattern always1587. **Neo4j relationships are verbs** — `FOLLOWS`, `PURCHASED`, `BELONGS_TO`, never nouns1598. **Migrations are immutable** — never edit a deployed migration, always create a new one1609. **Document everything** — inline SQL comments on non-obvious columns, README per schema dir