database-architect

Name: database-architect
Author: tranhieutt

69stars41forks

How to install database-architect?

tranhieutt/software_development_department/database-architect

$npx -y skills add tranhieutt/software_development_department --skill database-architect

Installs into the current project.

Summary

Designs relational and NoSQL database schemas, indexing strategies, migration plans, and data modeling patterns. Use when designing a database or when the user mentions database architecture, schema design, or data modeling.

Files · 1

View on GitHub

SKILL.md

1	# Database Architect
2
3	## Workflow
4
5	1. Understand domain: Access patterns, scale targets, consistency needs, compliance requirements
6	2. Select technology: Match DB type to workload (see matrix below)
7	3. Design schema: Normalization level, relationships, constraints, temporal data strategy
8	4. Plan indexing: Query-pattern-driven index design (not speculative)
9	5. Design caching: Layer strategy with invalidation
10	6. Plan migration: Zero-downtime approach, rollback procedures
11	7. Document decisions: ADR with rationale and trade-offs
12
13	## Technology selection matrix
14
15	\| Workload \| Primary choice \| Alternative \|
16	\|---\|---\|---\|
17	\| OLTP / relational \| PostgreSQL \| MySQL \|
18	\| Flexible documents \| MongoDB \| Firestore \|
19	\| Key-value / cache \| Redis \| DynamoDB \|
20	\| Time-series / IoT \| TimescaleDB \| InfluxDB \|
21	\| Analytical / OLAP \| ClickHouse \| BigQuery \|
22	\| Graph relationships \| Neo4j \| Amazon Neptune \|
23	\| Full-text search \| Elasticsearch \| Meilisearch \|
24	\| Globally distributed \| CockroachDB \| Google Spanner \|
25	\| Multi-tenant SaaS \| PostgreSQL (row-level security) \| Schema-per-tenant \|
26
27	Decision rule: Choose PostgreSQL by default; deviate only when access patterns demand it with documented rationale.
28
29	## Non-obvious rules
30
31	- Normalize first, denormalize with evidence — premature denormalization creates update anomalies; measure before optimizing
32	- Index on access patterns, not columns — index the query, not the table; one slow-query explain plan is worth more than any speculation
33	- Foreign keys always — letting the application enforce referential integrity is a data corruption waiting to happen
34	- JSONB for flexible attributes, not as a schema escape hatch — use JSONB when fields are genuinely variable; not to avoid schema discipline
35	- Partition late — partition tables only once you have row counts >50M or explicit I/O pressure; early partitioning adds complexity with zero benefit
36	- UUID v7 over v4 — v7 is time-ordered (k-sortable), avoids index fragmentation, same uniqueness guarantees
37
38	## Schema design patterns
39
40	```sql
41	-- Multi-tenancy: row-level security (best for <1000 tenants, shared infra)
42	ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
43	CREATE POLICY tenant_isolation ON orders
44	USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
45
46	-- Soft delete + audit trail (never DELETE production data)
47	ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
48	ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id);
49	CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;
50
51	-- Temporal / slowly-changing dimensions
52	CREATE TABLE product_prices (
53	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
54	product_id UUID NOT NULL REFERENCES products(id),
55	price NUMERIC(10,2) NOT NULL,
56	valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
57	valid_until TIMESTAMPTZ -- NULL = current price
58	);
59	```
60
61	## Indexing rules
62
63	```sql
64	-- Composite index: most selective column FIRST
65	CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);
66
67	-- Partial index: filter out the 95% noise
68	CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
69
70	-- Covering index: index-only scan (no heap access)
71	CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, avatar_url);
72
73	-- JSONB GIN index for flexible attribute queries
74	CREATE INDEX idx_metadata_gin ON events USING gin(metadata jsonb_path_ops);
75	```
76
77	## Migration strategy (non-negotiable steps)
78
79	```sql
80	-- 1. Expand: add new column nullable (no lock)
81	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
82
83	-- 2. Backfill: batch update (never one giant UPDATE)
84	UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN x AND y;
85
86	-- 3. Constrain: add NOT NULL only after backfill complete
87	ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
88
89	-- 4. Switch: deploy code using new column
90	-- 5. Contract: drop old column in separate release
91	ALTER TABLE users DROP COLUMN old_phone;
92	```
93
94	Zero-downtime rule: Never add a NOT NULL column without a default in a single migration on a live table — it acquires an ACCESS EXCLUSIVE lock.
95
96	## Caching architecture
97
98	\| Layer \| Tool \| Strategy \| Invalidation \|
99	\|---\|---\|---\|---\|
100	\| Hot data \| Redis \| Cache-aside \| TTL + event-driven \|
101	\| Query results \| PostgreSQL materialized views \| Refresh on schedule \| `REFRESH MATERIALIZED VIEW CONCURRENTLY` \|
102	\| Session data \| Redis \| Write-through \| TTL \|
103	\| Static references \| App memory \| Eager load on startup \|

People who liked this also viewed

6 picks

Type

database-architect

69stars41forks

View on GitHub

How to install database-architect?

tranhieutt/software_development_department/database-architect

$npx -y skills add tranhieutt/software_development_department --skill database-architect

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Database Architect
2
3	## Workflow
4
5	1. Understand domain: Access patterns, scale targets, consistency needs, compliance requirements
6	2. Select technology: Match DB type to workload (see matrix below)
7	3. Design schema: Normalization level, relationships, constraints, temporal data strategy
8	4. Plan indexing: Query-pattern-driven index design (not speculative)
9	5. Design caching: Layer strategy with invalidation
10	6. Plan migration: Zero-downtime approach, rollback procedures
11	7. Document decisions: ADR with rationale and trade-offs
12
13	## Technology selection matrix
14
15	\| Workload \| Primary choice \| Alternative \|
16	\|---\|---\|---\|
17	\| OLTP / relational \| PostgreSQL \| MySQL \|
18	\| Flexible documents \| MongoDB \| Firestore \|
19	\| Key-value / cache \| Redis \| DynamoDB \|
20	\| Time-series / IoT \| TimescaleDB \| InfluxDB \|
21	\| Analytical / OLAP \| ClickHouse \| BigQuery \|
22	\| Graph relationships \| Neo4j \| Amazon Neptune \|
23	\| Full-text search \| Elasticsearch \| Meilisearch \|
24	\| Globally distributed \| CockroachDB \| Google Spanner \|
25	\| Multi-tenant SaaS \| PostgreSQL (row-level security) \| Schema-per-tenant \|
26
27	Decision rule: Choose PostgreSQL by default; deviate only when access patterns demand it with documented rationale.
28
29	## Non-obvious rules
30
31	- Normalize first, denormalize with evidence — premature denormalization creates update anomalies; measure before optimizing
32	- Index on access patterns, not columns — index the query, not the table; one slow-query explain plan is worth more than any speculation
33	- Foreign keys always — letting the application enforce referential integrity is a data corruption waiting to happen
34	- JSONB for flexible attributes, not as a schema escape hatch — use JSONB when fields are genuinely variable; not to avoid schema discipline
35	- Partition late — partition tables only once you have row counts >50M or explicit I/O pressure; early partitioning adds complexity with zero benefit
36	- UUID v7 over v4 — v7 is time-ordered (k-sortable), avoids index fragmentation, same uniqueness guarantees
37
38	## Schema design patterns
39
40	```sql
41	-- Multi-tenancy: row-level security (best for <1000 tenants, shared infra)
42	ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
43	CREATE POLICY tenant_isolation ON orders
44	USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
45
46	-- Soft delete + audit trail (never DELETE production data)
47	ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
48	ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id);
49	CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;
50
51	-- Temporal / slowly-changing dimensions
52	CREATE TABLE product_prices (
53	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
54	product_id UUID NOT NULL REFERENCES products(id),
55	price NUMERIC(10,2) NOT NULL,
56	valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
57	valid_until TIMESTAMPTZ -- NULL = current price
58	);
59	```
60
61	## Indexing rules
62
63	```sql
64	-- Composite index: most selective column FIRST
65	CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);
66
67	-- Partial index: filter out the 95% noise
68	CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
69
70	-- Covering index: index-only scan (no heap access)
71	CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, avatar_url);
72
73	-- JSONB GIN index for flexible attribute queries
74	CREATE INDEX idx_metadata_gin ON events USING gin(metadata jsonb_path_ops);
75	```
76
77	## Migration strategy (non-negotiable steps)
78
79	```sql
80	-- 1. Expand: add new column nullable (no lock)
81	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
82
83	-- 2. Backfill: batch update (never one giant UPDATE)
84	UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN x AND y;
85
86	-- 3. Constrain: add NOT NULL only after backfill complete
87	ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
88
89	-- 4. Switch: deploy code using new column
90	-- 5. Contract: drop old column in separate release
91	ALTER TABLE users DROP COLUMN old_phone;
92	```
93
94	Zero-downtime rule: Never add a NOT NULL column without a default in a single migration on a live table — it acquires an ACCESS EXCLUSIVE lock.
95
96	## Caching architecture
97
98	\| Layer \| Tool \| Strategy \| Invalidation \|
99	\|---\|---\|---\|---\|
100	\| Hot data \| Redis \| Cache-aside \| TTL + event-driven \|
101	\| Query results \| PostgreSQL materialized views \| Refresh on schedule \| `REFRESH MATERIALIZED VIEW CONCURRENTLY` \|
102	\| Session data \| Redis \| Write-through \| TTL \|
103	\| Static references \| App memory \| Eager load on startup \|

People who liked this also viewed

6 picks

Type