# Database Design Principles This document resolves `../architecture/06-open-questions.md` item 2: boundary enforcement was documentation-only through Phase 1; here it becomes mechanical. ## One database, one schema per module CommerceDex uses a single PostgreSQL database (per `../architecture/03-reference-architecture.md`), with **one Postgres schema per backend module** — `catalog`, `inventory`, `orders`, `customers`, `payments`, `shipping`, `tax`, `marketing`, `discounts`, `automation`, `notifications`, `media`, `content`, `analytics`, `reporting`, `merchant_health`, `users`, `settings`, `search`. Commerce Core has no schema of its own (see `../architecture/04-domain-modules.md` — it's pure orchestration, not a data owner); it reads/writes through other modules' interfaces only. A `system` schema (owned by no business module) holds the shared event/job mechanism described in `03-event-and-job-system.md`. ## Enforcement mechanism (resolves the Phase 1 open question) Two layers, because either alone is insufficient: 1. **Database-level: Postgres role-based grants.** Each module's application code connects using a Postgres role that has full read/write on its own schema and **no privileges on any other module's schema**. A bug or a shortcut that tries to query another module's tables directly fails at the database connection level, not just at code review. This is a hard technical boundary, not a convention. 2. **Application-level: a dependency-boundary linter.** Within the application codebase, each module lives in its own top-level directory (e.g., `modules/catalog/`, `modules/inventory/`). A lint rule (dependency-cruiser, ESLint's `import/no-restricted-paths`, or equivalent for the chosen stack) forbids any module from importing another module's internal files — only its declared public interface (`04-module-interfaces.md`) may be imported. This catches boundary violations at commit time, before they ever reach a database query. Together: the linter stops a developer from *writing* a cross-module shortcut; the database grants stop it from *running* even if it somehow got past review. This is the concrete mechanism `../architecture/06-open-questions.md` #2 asked Phase 3 to decide. ## Naming conventions - **Tables:** snake_case, singular-noun-based on the entity (`product`, `order`, `stock_level`), namespaced implicitly by schema (`catalog.product`, not `catalog.catalog_product`). - **Primary keys:** every table has a single-column primary key named `id`, typed as a **ULID** (26-character, lexicographically sortable, timestamp-prefixed identifier) stored as text or a native type if the database supports one. ULIDs are chosen over auto-increment integers (which leak record counts and collide across restore/merge scenarios) and over random UUIDv4 (which fragments index locality). Sortable-by-creation IDs are a meaningful convenience in a single-tenant system with no cross-instance ID collision risk to worry about. - **Foreign keys:** named `_id` (`product_id`, `customer_id`). Foreign keys crossing schema boundaries (e.g., an `orders.order_line_item` row referencing a `catalog.product`) are **stored as plain ID values, not database-level `FOREIGN KEY` constraints** — Postgres would technically allow a cross-schema constraint, but that would re-introduce the tight coupling the module boundary exists to prevent (a schema migration in Catalog could be blocked by a constraint living in Orders). Referential integrity across module boundaries is the calling module's responsibility, enforced through the interface layer, not the database. - **Timestamps:** every table has `created_at` and `updated_at` (UTC, timezone-aware). Tables representing mutable business state that's ever corrected (not just updated) also carry `deleted_at` for soft deletes, rather than hard-deleting rows that other modules' events may still reference historically. - **Money:** stored as integer minor units (cents), never floating point, paired with a currency code column where the amount isn't unambiguously the store's single configured currency. ## Migrations Each module owns its own migration history, versioned independently, applied in dependency order at deploy time (Settings and other zero-dependency modules first, per the dependency graph in `../architecture/04-domain-modules.md`'s summary table). A single migration runner applies all pending migrations across all module schemas as one deploy step — this stays consistent with the single-deployable model in `../architecture/03-reference-architecture.md`; there is no independent per-module deployment yet, only independent per-module *schema evolution*. ## No tenant column, anywhere Per `../architecture/01-product-vision.md`, CommerceDex is single-tenant per install. No table in any schema carries a `tenant_id` or `store_id` column — the entire database belongs to one store. This is a deliberate simplification enabled by the deployment model in `../architecture/03-reference-architecture.md`, and it should stay that way; introducing multi-tenancy later would be a significant schema change across every module, not a config flag (see `../architecture/06-open-questions.md` #7 — this is intentional, not an oversight). ## Read access across modules A module that needs to *read* (not join against) another module's data for display purposes (e.g., Orders showing a product name on a line item) does so through that module's public interface (`04-module-interfaces.md`), which may return a denormalized read model — not a live join. This is consistent with the "snapshot, not live reference" rule in `../architecture/05-module-interaction-model.md`.