g14wxz/commerce-database-architect

Database architecture skills, docs, and rules for high-demand multi-tenant commerce platforms (PostgreSQL source of truth, Neo4j as derived GraphRAG projection, transactional outbox, RLS-based tenant isolation). Includes live schema introspection workflow via Postgres MCP.

Quality

97%

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Passed

No known issues

Introspection Queries

Name: g14wxz/commerce-database-architect
Rating: 97.27272727272728 (1 reviews)
Author: g14wxz

Per-table inspection set. Run these via the read-only Postgres MCP server.

Columns, defaults, nullability

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = $1 AND table_name = $2
ORDER BY ordinal_position;

Constraints (PK, UQ, CK, FK)

SELECT con.conname, con.contype,
       pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = $1 AND rel.relname = $2;

Indexes (with partial predicates)

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = $1 AND tablename = $2;

RLS policies (USING + WITH CHECK)

SELECT polname, pg_get_expr(polqual, polrelid) AS using_clause,
       pg_get_expr(polwithcheck, polrelid) AS with_check_clause
FROM pg_policy
WHERE polrelid = ($1 || '.' || $2)::regclass;

Triggers

SELECT tgname, tgtype, pg_get_triggerdef(oid) AS definition
FROM pg_trigger
WHERE tgrelid = ($1 || '.' || $2)::regclass
  AND NOT tgisinternal;

Incoming foreign keys (who references this table)

SELECT conrelid::regclass AS from_table, conname,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE confrelid = ($1 || '.' || $2)::regclass AND contype = 'f';

Outgoing foreign keys

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = ($1 || '.' || $2)::regclass AND contype = 'f';

Approximate row count and size

SELECT n_live_tup AS rows, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE schemaname = $1 AND relname = $2;

JSONB key distribution sample

SELECT jsonb_object_keys(metadata) AS key, count(*)
FROM <table> TABLESAMPLE SYSTEM (1)
GROUP BY 1 ORDER BY 2 DESC LIMIT 50;

Slow queries from `pg_stat_statements`

SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
WHERE query ILIKE $1
ORDER BY mean_exec_time DESC LIMIT 20;

g14wxz/commerce-database-architect