CtrlK
BlogDocsLog inGet started
Tessl Logo
PluginPublic

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 explicit Supabase MCP/read-only schema sources.

Quality

96%

Does it follow best practices?

Run evals on this skill

Adds up to 20 points to the overall score

View guide
SecuritybySnyk

Passed

No findings from the security scan

Overview
Quality
Evals
Security
Files

SKILL.mdskills/commerce-database-architecture/

name:
commerce-database-architecture
description:
Use when designing or reviewing database architecture for high-demand multi-tenant commerce platforms — including PostgreSQL schema design, foreign keys, indexes, JSONB usage, multi-tenant isolation with Row Level Security, transactional outbox, Neo4j GraphRAG projections, event sourcing decisions, audit logging, partitioning, expand/contract migrations, and product/inventory/order modeling for restaurants, boutiques, drugstores, retailers, distributors, grocery, hardware, or appliance businesses. Triggered by any request to design tables, design schemas, create migrations, model products/variants/inventory/orders/payments, choose between monolith and microservices, choose between PostgreSQL and Neo4j as source of truth, model multi-tenant data, design event flows, or review an ER diagram.
license:
MIT

Commerce Database Architecture

Protect the architecture while helping the user design schemas, specs, plans, and migrations.

Required context (load every time)

Before answering, read in order from .specify/memory/:

  1. constitution.md — non-negotiable principles
  2. architecture-decisions.md — recorded ADRs
  3. database-architecture-principles.md — operational rules
  4. graph-ai-boundaries.md — Neo4j boundaries
  5. eventing-and-audit-principles.md — outbox + audit
  6. current-schema-state.md — live snapshot (refresh via postgres-schema-introspection if > 24 h stale)
  7. schema-change-log.md — last 20 entries

Default architecture

Modular Monolith
  + PostgreSQL (≥ 16) as ACID source of truth
  + tenant_id + Row Level Security
  + Transactional Outbox for domain events
  + Neo4j (≥ 5) as derived GraphRAG / search / recommendation projection

Deviation requires an ADR. Invoke adr-drafting and refuse to proceed until the ADR exists.

Reasoning process

  1. Frame the change. Pick the domain and modules from domains-and-modules.md. Pick the transactional pattern: external call → short tx + outbox + short tx; replay/temporal → Event Sourcing candidate; cross-module notification → outbox event.

  2. Validate against the constitution. Walk § IV (tenancy), § V (product modeling), § VI (inventory integrity), § VIII (eventing), § X (Neo4j boundary), § XI (high-demand SQL).

  3. Produce the answer with the structured output below, grounded in canonical-ddl.md.

Output structure

## Recommendation
(one paragraph)

## Affected modules and tables
(bullet list)

## Schema (Mermaid erDiagram)
(diagram)

## Key indexes and constraints
(bullet list, justify each index)

## Tenant isolation
(RLS policy + composite unique constraints)

## Eventing
(emitted outbox events + Neo4j projection delta)

## Migration safety
(expand/contract steps if existing schema)

## Tests
(unit, integration, RLS, concurrency, projection)

## Open questions

Non-negotiables

Refuse toBecause
Recommend microservices, database-per-service, or Event Sourcing as default§ I, § III, § IX
Put prices, stock, totals, tenant IDs, payment amounts in JSONB§ V
Omit tenant_id or skip RLS on a tenant-scoped table§ IV
Skip the outbox for a domain event§ VIII
Recommend Neo4j as source of truth or let GraphRAG mutate transactional state§ X
Propose destructive migrations without expand/contract framing§ XII
Generate ALTER TABLE without checking current-schema-state.md§ XIV
Choose JSONB silently to avoid migration work§ V, schema-evolution discipline

Examples

  • "Design a schema for restaurant menu items with modifiers." — domain restaurant, module catalog. Produce catalog_menu_items, catalog_modifiers, catalog_modifier_groups, catalog_menu_item_modifier_groups (M:N) using the patterns in canonical-ddl.md. FK to base products if raw inventory is tracked.

    Minimal inline shape (full DDL in canonical-ddl.md):

    CREATE TABLE catalog_menu_items (
        id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        tenant_id   uuid NOT NULL REFERENCES tenants(id),
        sku         text NOT NULL,
        name        text NOT NULL,
        price_cents integer NOT NULL CHECK (price_cents >= 0),
        UNIQUE (tenant_id, sku)
    );
    ALTER TABLE catalog_menu_items ENABLE ROW LEVEL SECURITY;
    CREATE POLICY tenant_isolation ON catalog_menu_items
      USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
    erDiagram
      CATALOG_MENU_ITEMS ||--o{ CATALOG_MENU_ITEM_MODIFIER_GROUPS : has
      CATALOG_MODIFIER_GROUPS ||--o{ CATALOG_MENU_ITEM_MODIFIER_GROUPS : groups
      CATALOG_MODIFIER_GROUPS ||--o{ CATALOG_MODIFIERS : contains
  • "Should we split inventory into its own service?" — default is monolith. Demand a concrete trigger (write throughput, p99, on-call). If none, keep as a module. If real, invoke adr-drafting.

  • "[Mermaid ER diagram pasted]" — switch to the mermaid-diagram-review skill; it owns the five-section structured response.

skills

tile.json