Database Non-Negotiables

These rules are always on, applied to every interaction in this project, regardless of which command or skill is active. They are the minimum bar below which the agent never produces output.

If any rule conflicts with a user instruction, the rule wins and the agent explains why.

---

Architectural defaults

1. The default architecture is modular monolith + PostgreSQL + RLS + transactional outbox + Neo4j as derived projection. Deviation requires an ADR.
2. PostgreSQL is the source of truth. Neo4j, search indexes, caches, and analytics stores are derived. When they disagree, PostgreSQL wins.
3. Microservices are not a default. Propose extraction only after measured evidence (write throughput, replication lag, deploy cadence conflict, regulatory boundary, team-size driver).

Multi-tenant isolation

4. Every tenant-scoped table includes tenant_id BIGINT NOT NULL.
5. Every tenant-scoped composite index is tenant-first: (tenant_id, ...).
6. Every tenant-scoped unique constraint includes tenant_id: UNIQUE (tenant_id, sku), never UNIQUE (sku).
7. Row Level Security is on for every table holding user-visible data. The policy uses current_setting('app.tenant_id').
8. Frontend filtering is never sufficient. Tenant isolation lives at four layers: app authz, RLS, tenant-aware indexes, tenant-aware constraints.

Schema discipline

9. No table exceeds ~40 columns. If you're approaching that, model it as sub-aggregates.
10. Every table has created_at TIMESTAMPTZ and updated_at TIMESTAMPTZ, both NOT NULL DEFAULT now(). updated_at is trigger-maintained.
11. All timestamps are TIMESTAMPTZ. Never TIMESTAMP. Never epoch integers. Never strings.
12. Primary keys are BIGINT GENERATED ALWAYS AS IDENTITY for internal use. External IDs are UUID or ULID via a separate public_id column.
13. Foreign keys are mandatory. Removing one for "performance" requires an ADR with measured evidence.

JSONB

14. JSONB is for flexible, optional, non-critical metadata.
15. JSONB is forbidden for: prices, stock balances, payment amounts, tenant ownership, order totals, tax-critical fields, or any field used in joins, constraints, or frequent queries.
16. JSONB keys that become business-critical or frequently queried must be promoted to real columns and the JSONB key dropped.

Eventing

17. Every domain event leaving PostgreSQL is written to outbox_messages in the same transaction as the state change. Never publish-after-commit.
18. The outbox relay uses FOR UPDATE SKIP LOCKED.
19. External API calls happen outside DB transactions. Pattern: short tx (write intent + outbox) → commit → external call → short tx (record result).
20. Event consumers are idempotent. Exactly-once delivery is never assumed.

Inventory & financial integrity

21. Inventory balance changes are backed by an append-only inventory_movements row written in the same transaction.
22. Overselling prevention is mandatory: SELECT FOR UPDATE on the balance row, or inventory_reservations with a unique constraint, or optimistic concurrency with retry. Pick one per feature; document it.
23. Order totals, payment amounts, and refund amounts use NUMERIC(p, s), never FLOAT or DOUBLE.

Migration safety

24. Production migrations follow expand/contract: nullable add → batched backfill → cutover → enforce/drop.
25. Indexes on production-sized or live-traffic tables use CREATE INDEX CONCURRENTLY, or an explicit operator plan that builds the equivalent index concurrently before an IF NOT EXISTS migration no-ops.
26. No ALTER TABLE that rewrites the table without an ADR.
27. Every migration has a documented rollback step.
28. Forward-apply + dbsync is the production-relevant validation path. Start from an existing local/staging database, apply pending migrations without reset, run targeted tests, run /speckit.dbsync, and verify the snapshot reflects the intended schema.
29. supabase db reset --local is only a local rebuildability check. It never substitutes for forward-apply + dbsync when judging production readiness.
30. Dev, production, and live-like databases are never reset, reseeded, truncated, or rebuilt as deployment. They move forward through migrations only.
31. A Supabase-touching spec is not finished until the whole change set is validated locally, then forward-applied/deployed to dev and synced with /speckit.dbsync dev, then forward-applied/deployed to prod and synced with /speckit.dbsync prod after explicit Carlos production authorization. Dev must be green before prod is considered.
32. Specs that touch Supabase runtime surface must end with a local validation bundle: forward-apply migrations on an existing local DB, run targeted DB tests, locally smoke/contract-test touched Edge Functions, validate touched Storage/Realtime/config/type surfaces where local Supabase supports them, then run /speckit.dbsync local and label anything requiring staging or operator validation explicitly.

Neo4j boundary

33. Neo4j may store derived nodes, derived relationships, and stale-tolerant denormalized properties. It must not store transactional truth.
34. Sync flow is one-way: PostgreSQL → outbox → projection worker → Neo4j.
35. Before any commitment (checkout, reservation, payment, regulated-product sale), the system re-checks PostgreSQL truth.
36. GraphRAG output is suggestion-quality. The user or an authorized service action confirms the actual transaction.

Compliance

37. Pharmacy/regulated workflows default to the stricter interpretation when ambiguity exists.
38. The system never assists in bypassing legal controls, prescription requirements, age restrictions, or compliance checks.

When in doubt

If a user request would force a violation, stop and explain, then propose the constitutional alternative. If the user wants to override, require an ADR via /speckit.adr.