# MONNA SQL SCHEMA → DOMAIN ONTOLOGY MAPPER™ v2.0 ### Framework: MONNA SIGNATURE™ v11.1 | Module F: OntologyEngine + DDD Lens --- ``` SCHEMA ANALYSIS: 4 tables | 20 columns SAMPLE DATA: 4 rows (1 per table) | Coverage: SPARSE INPUT TYPE: DDL | Completeness: PARTIAL (FKs present; no CHECK constraints; no indexes; no triggers visible) DIALECT: postgres DOMAIN: e-commerce order platform {Evidence: DOMAIN_HINT} ``` --- ## ── LANE A: OBDA FINDINGS ── --- ### STEP A0 — INPUT CLASSIFICATION & ROUTING | Signal | Assessment | |---|---| | Input modality | DDL (CREATE TABLE × 4) | | FK relationships | Explicit — HIGH confidence | | CHECK constraints | None present | | Indexes | None declared beyond PK/UNIQUE | | Triggers | None visible | | Completeness | **PARTIAL** — schema is self-consistent but lacks status enumerations, product catalog, and audit/history tables | --- ### STEP A1 — TABLE ROLE CLASSIFICATION ``` TABLE: customers ROLE: ENTITY CONFIDENCE: HIGH EVIDENCE: {customer_id INT PRIMARY KEY} — surrogate identifier; {email VARCHAR(255) UNIQUE NOT NULL} — natural key candidate; {full_name NOT NULL} — descriptive label property; {created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP} — registration anchor HYPOTHESIS: Represents a registered account/buyer in the platform. "Customer" is the persistent identity across orders. {H-001} GAP: No address, phone, or locale column. Shipping/billing address must live elsewhere (unmapped table) or is inlined into orders (not evidenced here). {G-001} ``` ``` TABLE: orders ROLE: EVENT / TRANSACTION TABLE CONFIDENCE: HIGH EVIDENCE: {placed_at TIMESTAMP NOT NULL} — business-event temporal anchor; {order_status VARCHAR(50) NOT NULL} — lifecycle state pool; {customer_id FK → customers} — role reference (buyer); {total_amount DECIMAL(10,2)} — transaction measure HYPOTHESIS: Records the act of a customer placing a purchase commitment. A single Order is the unit of commercial transaction. {H-002} GAP: order_status has no CHECK constraint — valid state values unknown from schema alone. Sample data provides ONE value ("SHIPPED") only. {G-002} No shipping address column — unclear where delivery destination lives. {G-003} No updated_at column — last state transition time is unrecoverable without a separate audit table. {G-004} ``` ``` TABLE: order_items ROLE: ENTITY TABLE (Weak Entity / Aggregate Member) CONFIDENCE: HIGH EVIDENCE: {order_item_id INT PRIMARY KEY} — surrogate PK; {order_id FK → orders} — mandatory parent reference (NOT NULL); {product_sku VARCHAR(50) NOT NULL} — natural product reference; {quantity INT NOT NULL} — measure; {unit_price DECIMAL(10,2) NOT NULL} — snapshotted price measure HYPOTHESIS: Represents a line item within an order. Meaningless without parent Order. unit_price appears to be a price snapshot at time of order (not a live reference to a product catalog). {H-003} GAP: product_sku references a product domain that has NO table in this schema. No FK to a products table — either schema is incomplete or SKU is treated as an opaque identifier. {G-005} No discount or tax column — unclear how total_amount is computed from line items. {G-006} ``` ``` TABLE: payments ROLE: EVENT TABLE CONFIDENCE: HIGH EVIDENCE: {payment_id INT PRIMARY KEY}; {order_id FK → orders} — links payment act to order; {paid_at TIMESTAMP} — business-event temporal anchor (NULLABLE); {payment_status VARCHAR(50)} — lifecycle state pool (NULLABLE); {payment_method VARCHAR(50)} — actor/instrument descriptor (NULLABLE) HYPOTHESIS: Records the payment attempt/confirmation event for an order. paid_at being nullable suggests it is written on capture, not on initiation. {H-004} GAP: payment_status is NULLABLE — no CHECK constraint. Sample shows "Captured"; other states (Pending, Failed, Refunded) are hypothetical. {G-007} payment_method is NULLABLE — incomplete payment records are possible; reason for nullability is unknown. {G-008} No amount column on payments — unclear if partial payments are possible or if payment is always for full order total. {G-009} One-to-one or one-to-many between orders and payments is ambiguous — schema allows multiple payments per order (no UNIQUE on order_id). {G-010} ``` --- ### STEP A2 — COLUMN SEMANTIC MAPPING | Table | Column | Semantic Role | Confidence | Notes | |---|---|---|---|---| | customers | customer_id | IDENTIFIER (surrogate PK) | HIGH | INT PK | | customers | full_name | DATA PROPERTY — label | HIGH | NOT NULL | | customers | email | IDENTIFIER (natural key candidate) | HIGH | UNIQUE NOT NULL | | customers | created_at | TEMPORAL ANCHOR — registration | HIGH | DEFAULT NOW() | | orders | order_id | IDENTIFIER (surrogate PK) | HIGH | INT PK | | orders | customer_id | OBJECT PROPERTY — FK to customers | HIGH | NOT NULL FK | | orders | order_status | STATE POOL | MEDIUM | No CHECK constraint — values unknown | | orders | total_amount | DATA PROPERTY — measure | HIGH | DECIMAL, NOT NULL | | orders | placed_at | TEMPORAL ANCHOR — business event | HIGH | NOT NULL | | order_items | order_item_id | IDENTIFIER (surrogate PK) | HIGH | INT PK | | order_items | order_id | OBJECT PROPERTY — FK to orders | HIGH | NOT NULL FK | | order_items | product_sku | WEAK SEMANTIC — external reference | MEDIUM | No FK; opaque string {G-005} | | order_items | quantity | DATA PROPERTY — measure | HIGH | INT NOT NULL | | order_items | unit_price | DATA PROPERTY — price snapshot | HIGH | DECIMAL NOT NULL | | payments | payment_id | IDENTIFIER (surrogate PK) | HIGH | INT PK | | payments | order_id | OBJECT PROPERTY — FK to orders | HIGH | NOT NULL FK | | payments | payment_method | DATA PROPERTY — instrument label | MEDIUM | NULLABLE; no enumeration constraint | | payments | payment_status | STATE POOL | MEDIUM | NULLABLE; no CHECK constraint | | payments | paid_at | TEMPORAL ANCHOR — capture event | MEDIUM | NULLABLE — only set on capture? | --- ### STEP A3 — RELATIONSHIP & CONSTRAINT EXTRACTION ``` RELATIONSHIP: orders.customer_id → customers.customer_id SEMANTIC: Order [is placed by] Customer CARDINALITY: many-to-one (one customer, many orders) CONFIDENCE: HIGH (explicit FK, NOT NULL) RELATIONSHIP: order_items.order_id → orders.order_id SEMANTIC: OrderItem [belongs to] Order CARDINALITY: many-to-one (one order, many items) CONFIDENCE: HIGH (explicit FK, NOT NULL) RELATIONSHIP: payments.order_id → orders.order_id SEMANTIC: Payment [fulfils / is applied to] Order CARDINALITY: many-to-one — schema ALLOWS multiple payments per order (no UNIQUE constraint on payments.order_id) CONFIDENCE: HIGH (explicit FK); cardinality interpretation = MEDIUM (business intent unknown — could be 1:1 enforced at app layer) ``` **BUSINESS RULE REGISTRY** (extracted from structural constraints): | Rule ID | Rule | Source | |---|---|---| | BR-001 | Every Order must reference a Customer | FK + NOT NULL on orders.customer_id | | BR-002 | Every OrderItem must reference an Order | FK + NOT NULL on order_items.order_id | | BR-003 | Every Payment must reference an Order | FK + NOT NULL on payments.order_id | | BR-004 | Customer email must be unique across the system | UNIQUE constraint | | BR-005 | An Order must have a status at all times | NOT NULL on order_status | | BR-006 | An Order must record its placement time | NOT NULL on placed_at | | BR-007 | Each OrderItem must record quantity and unit price | NOT NULL on both | | BR-008 | total_amount is mandatory on Order | NOT NULL | **No CHECK constraints present** — all state enumerations are unenforced at DB level. --- ### STEP A4 — PROVENANCE & GAP REGISTERS **EVIDENCE REGISTER:** | ID | Table.Column | Classification | Evidence | |---|---|---|---| | E-001 | customers.customer_id | Surrogate PK / Identifier | INT PRIMARY KEY | | E-002 | customers.email | Natural key candidate | UNIQUE NOT NULL | | E-003 | customers.created_at | Registration temporal anchor | TIMESTAMP DEFAULT CURRENT_TIMESTAMP | | E-004 | orders.order_status | State pool | VARCHAR(50) NOT NULL | | E-005 | orders.placed_at | Business-event temporal anchor | TIMESTAMP NOT NULL | | E-006 | orders.total_amount | Transaction measure | DECIMAL(10,2) NOT NULL | | E-007 | orders.customer_id | Role reference (buyer) | FK → customers + NOT NULL | | E-008 | order_items.order_id | Mandatory parent reference | FK + NOT NULL | | E-009 | order_items.unit_price | Price snapshot (not live ref) | DECIMAL NOT NULL; no FK to product | | E-010 | payments.paid_at | Capture temporal anchor | TIMESTAMP NULLABLE | | E-011 | payments.order_id | Payment-to-order linkage | FK NOT NULL | **HYPOTHESIS REGISTER:** | ID | Concept | Basis | Confidence | What Would Confirm | |---|---|---|---|---| | H-001 | Customer = registered buyer identity | Column semantics + domain hint | HIGH | User/account table linkage or auth FK | | H-002 | Order = unit of commercial transaction | Event columns + FK structure | HIGH | Workflow docs or state machine spec | | H-003 | unit_price = price snapshot at order time | No FK to products; DECIMAL NOT NULL | MEDIUM | Comment in DDL or product history table | | H-004 | paid_at written only on payment capture | NULLABLE paid_at | MEDIUM | Application-layer write logic | | H-005 | total_amount = sum of (quantity × unit_price) per item | Arithmetic from sample: 2 × 124.99 = 249.98 ✓ | HIGH | Computed column or app-layer validation rule | **GAP REGISTER (OBDA):** | ID | Missing Concept | Why It Matters | Where to Look | |---|---|---|---| | G-001 | Customer address (shipping/billing) | Core to fulfilment; absent from all tables | Unmapped table or orders table extension | | G-002 | Valid order_status values | Cannot model state machine without enumeration | Application code, CHECK constraint, or enum type | | G-003 | Shipping address on order | Delivery destination unknown | Unmapped orders_shipping or address table | | G-004 | Order state-change timestamps | Audit trail absent; "when did status change?" unanswerable | History/audit table or updated_at column | | G-005 | Product catalog | product_sku is orphaned — no products table | Separate schema, microservice, or external catalog | | G-006 | Discount / tax decomposition | total_amount derivation is opaque | order_items extension or order-level discount table | | G-007 | Valid payment_status values | "Captured" is observed; others unknown | Application code or payment gateway docs | | G-008 | Reason for payment_method nullability | Could indicate pending or failed records | Application write patterns | | G-009 | Payment amount column | Partial payment support unknown | payments table extension | | G-010 | 1:1 vs 1:N order-to-payment cardinality | Affects refund, split-payment modelling | UNIQUE constraint decision or business rule | --- ## ── LANE B: DDD FINDINGS ── --- ### STEP B0 — UBIQUITOUS LANGUAGE EXTRACTION *Source: DDL column names + sample data rows + domain hint "e-commerce order platform"* ``` UBIQUITOUS LANGUAGE CANDIDATES: Entities: - Customer {Evidence: table name, sample data "Sarah Ahmed"} - Order {Evidence: table name, sample data order_id 5001} - OrderItem {Evidence: table name, sample data order_item_id 9001} - Payment {Evidence: table name, sample data payment_id 7001} - Product {DATA-INFERRED: product_sku "KB-104" implies a product domain entity not represented in schema} {G-005} Value Objects (candidates): - Money {DATA-INFERRED: total_amount=249.98, unit_price=124.99 are decimal measures — currency unit is absent from schema; a proper Money VO would carry amount + currency} {G-011} - EmailAddress {DATA-INFERRED: email "sarah@example.com" — format-validated string; candidate VO if validation rules exist} - ProductSKU {DATA-INFERRED: "KB-104" — structured identifier string; may carry format rules (prefix = category code?)} - OrderStatus {DATA-INFERRED: "SHIPPED" — screaming-snake string; candidate Enumeration or Status VO} - PaymentStatus {DATA-INFERRED: "Captured" — mixed-case string; candidate Enumeration — NOTE: casing inconsistency vs OrderStatus suggests different authorship or domain} {G-012} Domain Events (from temporal columns + status values): - CustomerRegistered {placed_at equivalent: created_at on customers} - OrderPlaced {placed_at timestamp — strongest event signal} - OrderShipped {order_status = "SHIPPED" implies this happened} - PaymentCaptured {payment_status = "Captured" + paid_at timestamp} Enumerations (observed values from sample data): - OrderStatus: {"SHIPPED"} — only ONE value observed; full set unknown {G-002} - PaymentStatus: {"Captured"} — only ONE value observed {G-007} - PaymentMethod: {"CreditCard"} — one value; likely more exist Implicit Concepts (not present as column names, inferred from patterns): - Cart / Basket {HYPOTHESIS: pre-order state not evidenced in schema; orders.placed_at suggests order is post-cart commit} {H-006} - Fulfilment / Shipment {HYPOTHESIS: "SHIPPED" status implies a fulfilment process; no shipment table evidenced} {H-007} - Refund {HYPOTHESIS: payment_status likely has a "Refunded" state; entirely absent from schema} {H-008} - Currency {HYPOTHESIS: all monetary amounts lack currency column; system may be single-currency only} {H-009} ``` --- ### STEP B1 — AGGREGATE IDENTIFICATION ``` AGGREGATE: Order ROOT: orders table MEMBERS: - order_items (child records — orphaned without Order) - payments (event records linked to Order) INVARIANTS: I-001: total_amount must equal SUM(order_items.quantity × unit_price) {EVIDENCE: H-005 confirmed by sample arithmetic: 2×124.99=249.98 ✓} I-002: An Order must have at least one OrderItem to be valid {HYPOTHESIS — not enforced by schema; no CHECK or trigger} {D-001} I-003: order_status transitions follow a defined lifecycle {HYPOTHESIS — state machine not encoded in schema} {D-002} I-004: Payment capture should not exceed Order total {HYPOTHESIS — no amount on payments; unenforced} {D-003} EVIDENCE SOURCE: schema (FK structure) + sample data (arithmetic check) CONFIDENCE: HIGH (structural) | MEDIUM (invariants — app-layer enforcement assumed) ────────────────────────────────────────────────────────── AGGREGATE: Customer ROOT: customers table MEMBERS: - (no child tables present in schema) - Orders are related but likely managed by Order Aggregate, not Customer INVARIANTS: I-005: Email must be unique per Customer {EVIDENCE: UNIQUE constraint — E-002} I-006: Customer must have full_name at registration {EVIDENCE: NOT NULL} EVIDENCE SOURCE: schema constraints CONFIDENCE: HIGH ────────────────────────────────────────────────────────── AGGREGATE: Product (HYPOTHESISED — no table present) ROOT: (unmapped — product_sku is opaque reference) MEMBERS: (unknown) INVARIANTS: (unknown) EVIDENCE SOURCE: product_sku column in order_items {DATA-INFERRED} CONFIDENCE: LOW — requires domain expert confirmation and schema completion ``` --- ### STEP B2 — BOUNDED CONTEXT MAP ``` BOUNDED CONTEXT: OrderManagement OWNS: - Order (Aggregate Root) - OrderItem (Aggregate Member) - Payment (Domain Event recorder within order lifecycle) SHARED KERNEL: - customer_id (referenced by orders; Customer entity owned by CustomerManagement context — coupling risk if Customer is modified) - product_sku (referenced by order_items; Product entity owned by Catalog context — coupling risk if SKU format changes) ANTI-CORRUPTION LAYER NEEDED: YES Rationale: orders.customer_id and order_items.product_sku are cross-context references with no FK enforcement to owning tables. If CustomerManagement or ProductCatalog evolve independently, OrderManagement must translate, not absorb, their identifiers. UBIQUITOUS LANGUAGE NOTE: "Order" here means a confirmed purchase commitment with items and payment. Not a quote, not a basket, not a draft. ────────────────────────────────────────────────────────── BOUNDED CONTEXT: CustomerManagement OWNS: - Customer (Aggregate Root) SHARED KERNEL: - customer_id exported to OrderManagement ANTI-CORRUPTION LAYER NEEDED: YES (from OrderManagement's perspective) UBIQUITOUS LANGUAGE NOTE: "Customer" = registered account identity. In a Support or Loyalty context, "Customer" may carry additional meaning (tickets, points) not evidenced here. {D-004} ────────────────────────────────────────────────────────── BOUNDED CONTEXT: ProductCatalog (HYPOTHESISED) OWNS: - Product (inferred from product_sku) {H-003, G-005} SHARED KERNEL: - product_sku exported to OrderManagement ANTI-CORRUPTION LAYER NEEDED: YES UBIQUITOUS LANGUAGE NOTE: ProductSKU in OrderManagement is a snapshot identifier. The live product price/description lives in ProductCatalog. unit_price in order_items is a deliberately denormalised snapshot — correct DDD practice. {DATA-INFERRED} ────────────────────────────────────────────────────────── BOUNDED CONTEXT: PaymentProcessing (potential split) NOTE: Payment is currently modelled as a member of OrderManagement. If payment processing involves external gateway integration, retry logic, or reconciliation, it may warrant its own context. CONFIDENCE: LOW — insufficient schema evidence to split definitively. RECOMMENDATION: Validate with domain expert. {D-005} ``` --- ### STEP B3 — DOMAIN EVENT REGISTER ``` DOMAIN EVENT: CustomerRegistered TRIGGER TABLE: customers TRIGGER SIGNAL: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP (insert event) PAYLOAD FIELDS: customer_id, full_name, email, created_at MISSING FIELDS: source channel (web/mobile/api), referral, locale, currency preference CONFIDENCE: HIGH (temporal anchor present, insert semantics clear) ────────────────────────────────────────────────────────── DOMAIN EVENT: OrderPlaced TRIGGER TABLE: orders TRIGGER SIGNAL: placed_at TIMESTAMP NOT NULL (business-event anchor) PAYLOAD FIELDS: order_id, customer_id, order_status (initial), total_amount, placed_at MISSING FIELDS: shipping address, currency, discount codes, channel CONFIDENCE: HIGH (placed_at is unambiguous business-event timestamp) ────────────────────────────────────────────────────────── DOMAIN EVENT: OrderStatusChanged (e.g., OrderShipped) TRIGGER TABLE: orders TRIGGER SIGNAL: order_status column — value transition (e.g., → "SHIPPED") PAYLOAD FIELDS: order_id, order_status (new value), (transition timestamp MISSING) MISSING FIELDS: previous_status, changed_at, changed_by, reason CONFIDENCE: MEDIUM (status column evidences state; no audit trail for transitions) {G-004} NOTE: "SHIPPED" is the only observed status. Full event vocabulary unknown. ────────────────────────────────────────────────────────── DOMAIN EVENT: PaymentCaptured TRIGGER TABLE: payments TRIGGER SIGNAL: paid_at TIMESTAMP (written on capture); payment_status = "Captured" PAYLOAD FIELDS: payment_id, order_id, payment_method, payment_status, paid_at MISSING FIELDS: amount captured, gateway transaction ID, currency, authorisation code CONFIDENCE: HIGH (paid_at + "Captured" status together are strong capture signals) ────────────────────────────────────────────────────────── DOMAIN EVENT: PaymentFailed (HYPOTHESISED) TRIGGER TABLE: payments TRIGGER SIGNAL: payment_status = (unknown failure value); paid_at = NULL PAYLOAD FIELDS: (same as above, paid_at absent) MISSING FIELDS: failure reason, retry count, gateway error code CONFIDENCE: LOW {H-008 adjacent} — inferred from paid_at nullability; not evidenced ``` --- ### STEP B4 — GAP REGISTER (DDD) | ID | DDD Concept | Gap Description | Impact on Domain Model | |---|---|---|---| | D-001 | Order invariant: minimum one item | No DB constraint enforces non-empty order | Empty orders are possible at persistence layer; domain invariant must live in application | | D-002 | Order state machine | Valid order_status transitions undefined in schema | Cannot model legal state graph; risk of invalid status values | | D-003 | Payment amount invariant | No amount on payments; cannot enforce "payment ≤ order total" | Overpayment or underpayment undetectable at DB level | | D-004 | Customer in multiple contexts | "Customer" may mean different things in Support, Loyalty, Analytics | Ubiquitous Language collision risk across future Bounded Contexts | | D-005 | Payment as separate context | PaymentProcessing may be a distinct Bounded Context | Current model may under-separate concerns if gateway integration exists | | D-006 | Anemic Domain Model signal | All tables are pure data containers; no behavioral hints (no triggers, no computed columns, no generated columns) | Domain logic entirely in application layer — risk of scattered, untested invariants | | D-007 | Missing Cart/Basket aggregate | No pre-order state evidenced | "Order" may be conflated with "Cart"; domain distinction unclear | | D-008 | Fulfilment / Shipment aggregate | "SHIPPED" status has no corresponding shipment record | Tracking numbers, carriers, delivery dates unmodellable | | D-009 | Currency Value Object absent | Monetary amounts have no currency denomination | Multi-currency support requires schema change; single-currency assumption unverified | | D-010 | Refund lifecycle absent | No refund table or status evidenced | Refund domain events and their effect on order/payment state are unmodellable | | D-011 | PaymentStatus casing inconsistency | "SHIPPED" (upper) vs "Captured" (mixed) — different authorship conventions | Ubiquitous Language incoherence; serialisation bugs possible | --- ## ── LANE C: VALIDATION & CONTRAST ── --- ### STEP C1 — CONVERGENCE ANALYSIS ``` ENTITY: customers OBDA CLASSIFICATION: ENTITY TABLE DDD CLASSIFICATION: Aggregate Root (Customer) CONVERGENCE: ALIGNED ALIGNMENT NOTE: Both lenses identify customers as a persistent, independently meaningful entity with a natural key (email). DIVERGENCE NOTE: None material. RESOLUTION: High confidence; no lens conflict. ────────────────────────────────────────────────────────── ENTITY: orders OBDA CLASSIFICATION: EVENT / TRANSACTION TABLE DDD CLASSIFICATION: Aggregate Root (Order) CONVERGENCE: PARTIAL ALIGNMENT NOTE: Both lenses agree orders is the central transactional entity. OBDA correctly identifies it as event-bearing (placed_at). DIVERGENCE NOTE: OBDA classifies it as EVENT table; DDD elevates it to Aggregate Root — the authoritative consistency boundary for OrderItems and Payments. These are not contradictory but complementary: "Order" is both a recorded business event AND an Aggregate Root. RESOLUTION: DDD lens is more precise here. An Aggregate Root can record an event at creation (OrderPlaced) while remaining the consistency owner for its members. ────────────────────────────────────────────────────────── ENTITY: order_items OBDA CLASSIFICATION: ENTITY TABLE (Weak Entity) DDD CLASSIFICATION: Aggregate Member (within Order Aggregate) CONVERGENCE: ALIGNED ALIGNMENT NOTE: OBDA's "Weak Entity" and DDD's "Aggregate Member" converge perfectly — both assert meaninglessness without parent. DIVERGENCE NOTE: None. RESOLUTION: Strongest convergence in the schema. ────────────────────────────────────────────────────────── ENTITY: payments OBDA CLASSIFICATION: EVENT TABLE DDD CLASSIFICATION: Aggregate Member (Order) — with candidate split into PaymentProcessing Bounded Context {D-005} CONVERGENCE: PARTIAL ALIGNMENT NOTE: Both agree payments is event-bearing (paid_at, status). DIVERGENCE NOTE: DDD raises the question of whether Payment is merely a member of Order Aggregate, or whether a PaymentProcessing context owns it. OBDA cannot surface this — it is a semantic/ organisational boundary question. RESOLUTION: DDD lens adds value here. Recommend domain expert validation on payment context ownership. {D-005} ``` --- ### STEP C2 — SEMANTIC ENRICHMENT DELTA ``` DDD REVEALS (not visible in schema structure alone): 1. Money as Value Object — monetary columns lack currency denomination; a Money VO (amount + currency) is the correct domain type {D-009} 2. OrderStatus and PaymentStatus as Enumerations — schema treats them as unconstrained VARCHAR; DDD demands explicit state machines 3. Order Aggregate invariant: total_amount = Σ(quantity × unit_price) {confirmed by sample arithmetic — H-005 elevated} 4. unit_price is a price snapshot VO — deliberate denormalisation from ProductCatalog context; correct DDD anti-corruption practice 5. Cart/Basket aggregate likely precedes Order in domain lifecycle {H-006} 6. Fulfilment/Shipment aggregate implied by "SHIPPED" status {H-007} 7. PaymentStatus casing inconsistency signals Ubiquitous Language drift {D-011} 8. Cross-context coupling risk: customer_id and product_sku are foreign identifiers with no FK to owning contexts — ACL needed OBDA CONFIRMS (structural ground truth): 1. BR-001 through BR-008: all mandatory participation rules confirmed by NOT NULL + FK constraints 2. Customer email uniqueness is DB-enforced — not just convention 3. OrderItem-to-Order and Payment-to-Order are hard FK relationships — not soft references; cascade behaviour unknown (not specified in DDL) 4. placed_at on orders is NOT NULL — OrderPlaced event is always recorded 5. paid_at on payments IS NULL — PaymentCaptured event is conditional OBDA CORRECTS DDD OVER-INFERENCE: 1. DDD might infer a 1:1 Order-to-Payment from domain intuition ("one order, one payment"). Schema allows 1:N — no UNIQUE constraint on payments.order_id. DDD assumption corrected by structural evidence. 2. DDD might treat product_sku as a live reference to a products table. OBDA confirms no FK exists — it is an opaque string, not a referential join target within this schema. DDD CORRECTS OBDA UNDER-INFERENCE: 1. OBDA labels order_status and payment_status as WEAK SEMANTIC (ambiguous, no CHECK constraint). Sample data resolves these to STATUS POOL with candidate Enumeration values — not truly weak, but unenforced at DB layer. 2. OBDA cannot detect the Money Value Object pattern — it sees DECIMAL columns. DDD + domain hint surfaces the missing currency dimension as a significant modelling gap. 3. OBDA cannot surface the Aggregate boundary between orders and order_items beyond "FK relationship." DDD identifies order_items as inside the Order Aggregate — not an independent entity — with invariants that OBDA cannot express. ``` --- ### STEP C3 — UNIFIED DOMAIN MODEL RECOMMENDATION ``` RECOMMENDED DOMAIN MODEL: Aggregates confirmed by both lenses: - Order (Root: orders; Members: order_items, payments) - Customer (Root: customers; Members: none in schema) Aggregates suggested by DDD only (schema-incomplete): - Product / ProductCatalog {LOW confidence — G-005, H-003} - Cart / Basket {HYPOTHESIS — H-006, D-007} - Shipment / Fulfilment {HYPOTHESIS — H-007, D-008} Ontology classes confirmed by OBDA: - Customer, Order, OrderItem, Payment (all four tables) - CustomerRegistered (event — created_at) - OrderPlaced (event — placed_at) - PaymentCaptured (event — paid_at + status) Classes needing domain expert validation: - Money Value Object (amount + currency) - OrderStatus enumeration (full set of valid values) - PaymentStatus enumeration (full set of valid values) - PaymentProcessing as separate Bounded Context - Refund lifecycle and its domain events MODELLING STRATEGY: DDD-first, OBDA-anchored Rationale: Schema is structurally clean and FK-complete for the four tables present, but PARTIAL overall (missing product, address, shipment, audit tables). DDD-first is appropriate because: (a) the domain is well-understood (e-commerce), (b) sample data resolves several OBDA weak-semantic gaps, (c) the most significant gaps (Money VO, state machines, Bounded Context boundaries) are semantic, not structural. OBDA serves as the structural anchor and constraint validator, preventing DDD from over-inferring relationships the schema does not enforce. NEXT STEPS FOR DOMAIN EXPERT: 1. [HIGHEST PRIORITY] Define the complete OrderStatus state machine — valid values, legal transitions, and which events trigger each. This unblocks both domain event modelling and DB CHECK constraint addition. {G-002, D-002} 2. [HIGH PRIORITY] Confirm Order-to-Payment cardinality intent: is one-payment-per-order a business rule enforced at application layer only? If yes, add UNIQUE(order_id) to payments. If no, define split-payment and refund semantics. {G-010, D-003} 3. [HIGH PRIORITY] Confirm whether Payment belongs to the Order Aggregate or is owned by a separate PaymentProcessing Bounded Context. This determines ACL requirements and event choreography design. {D-005} 4. [MEDIUM PRIORITY] Confirm currency handling: is the platform single-currency? If multi-currency is planned, a Money Value Object with currency code must be introduced before data volume makes migration costly. {D-009, H-009} 5. [MEDIUM PRIORITY] Locate or specify the Product/Catalog schema. product_sku is an orphaned reference — either document the external service owning it, or add a products table. {G-005} ``` --- ## ── DEEP OUTPUT EXTENSIONS ── --- ### STATE MACHINE HINTS ``` ENTITY: Order (orders.order_status) OBSERVED STATES: {SHIPPED} HYPOTHESISED FULL STATE MACHINE: [PENDING] → [CONFIRMED] → [PROCESSING] → [SHIPPED] → [DELIVERED] ↘ [CANCELLED] ↘ [CANCELLED] NOTE: All states except SHIPPED are HYPOTHESIS {H-010}. Evidenced transitions: NONE (no audit trail — G-004). Recommended: Add orders_status_history table with (order_id, from_status, to_status, changed_at, changed_by). ────────────────────────────────────────────────────────── ENTITY: Payment (payments.payment_status) OBSERVED STATES: {Captured} HYPOTHESISED FULL STATE MACHINE: [Pending] → [Authorised] → [Captured] ↘ [Failed] [Captured] → [Refunded] (partial or full) NOTE: All states except Captured are HYPOTHESIS {H-011}. paid_at = NULL suggests Pending/Failed states exist. Recommended: Add NOT NULL amount column and payment_gateway_reference VARCHAR to payments. ``` --- ### OWL CLASS SKETCHES (Turtle-style, top 3 entities) ```turtle @prefix : <https://ontology.example.com/ecommerce#> . @prefix owl: <http://www.w3.org/2002/07/owl#> . @prefix xsd: <http://www.w3.org/2001/XMLSchema#> . @prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> . ### Customer :Customer a owl:Class ; rdfs:label "Customer" ; rdfs:comment "A registered buyer identity on the platform." . :customerId a owl:DatatypeProperty ; rdfs:domain :Customer ; rdfs:range xsd:integer ; rdfs:comment "Surrogate primary key. {E-001}" . :email a owl:DatatypeProperty ; rdfs:domain :Customer ; rdfs:range xsd:string ; rdfs:comment "Unique natural key. Functional property. {E-002}" . # owl:FunctionalProperty candidate (UNIQUE constraint evidenced) :fullName a owl:DatatypeProperty ; rdfs:domain :Customer ; rdfs:range xsd:string . :registeredAt a owl:DatatypeProperty ; rdfs:domain :Customer ; rdfs:range xsd:dateTime ; rdfs:comment "Registration temporal anchor. {E-003}" . ### Order :Order a owl:Class ; rdfs:label "Order" ; rdfs:comment "A confirmed purchase commitment. Aggregate Root." . :placedBy a owl:ObjectProperty ; rdfs:domain :Order ; rdfs:range :Customer ; rdfs:comment "FK: orders.customer_id → customers.customer_id. {E-007}" . :hasOrderItem a owl:ObjectProperty ; rdfs:domain :Order ; rdfs:range :OrderItem ; rdfs:comment "Composition: OrderItem cannot exist without Order." . :hasPayment a owl:ObjectProperty ; rdfs:domain :Order ; rdfs:range :Payment . :orderStatus a owl:DatatypeProperty ; rdfs:domain :Order ; rdfs:range xsd:string ; rdfs:comment "State pool. Observed: SHIPPED. Full enumeration unknown. {G-002}" . :totalAmount a owl:DatatypeProperty ; rdfs:domain :Order ; rdfs:range xsd:decimal ; rdfs:comment "Transaction measure. Invariant: = Σ(quantity × unitPrice). {H-005}" . :placedAt a owl:DatatypeProperty ; rdfs:domain :Order ; rdfs:range xsd:dateTime ; rdfs:comment "OrderPlaced event temporal anchor. {E-005}" . ### OrderItem :OrderItem a owl:Class ; rdfs:label "OrderItem" ; rdfs:comment "Line item within an Order. Weak entity / Aggregate Member." . :productSku a owl:DatatypeProperty ; rdfs:domain :OrderItem ; rdfs:range xsd:string ; rdfs:comment "External product reference. No FK in schema. {G-005}" . :quantity a owl:DatatypeProperty ; rdfs:domain :OrderItem ; rdfs:range xsd:integer . :unitPrice a owl:DatatypeProperty ; rdfs:domain :OrderItem ; rdfs:range xsd:decimal ; rdfs:comment "Price snapshot at order time. Not a live product reference. {H-003}" . ``` --- ### DDD CONTEXT MAP (diagram-as-text) ``` ╔══════════════════════════════╗ ║ CustomerManagement ║ ║ ───────────────────── ║ ║ Aggregate: Customer ║ ║ Exports: customer_id ║ ╚══════════╤═══════════════════╝ │ customer_id (Shared Kernel) │ [ACL recommended] ▼ ╔══════════════════════════════════════════════╗ ║ OrderManagement ║ ║ ────────────────────────────────────── ║ ║ Aggregate Root: Order ║ ║ Members: OrderItem, Payment ║ ║ Domain Events: ║ ║ OrderPlaced, OrderStatusChanged, ║ ║ PaymentCaptured ║ ║ Imports: customer_id (from CustomerMgmt) ║ ║ Imports: product_sku (from ProductCatalog) ║ ╚══════════╤═══════════════════╤═══════════════╝ │ │ │ product_sku │ (possible future split) │ [ACL recommended] │ ▼ ▼ ╔═════════════════════╗ ╔══════════════════════════╗ ║ ProductCatalog ║ ║ PaymentProcessing ║ ║ ──────────────── ║ ║ ────────────────────── ║ ║ (HYPOTHESISED) ║ ║ (CANDIDATE — D-005) ║ ║ Aggregate: Product ║ ║ Gateway integration? ║ ║ Exports: SKU, ║ ║ Refund lifecycle? ║ ║ price list ║ ║ Reconciliation? ║ ╚═════════════════════╝ ╚══════════════════════════╝ LEGEND: ──── Confirmed relationship (schema-evidenced) ···· Hypothesised relationship (DDD-inferred, not schema-evidenced) [ACL] Anti-Corruption Layer recommended ``` --- ### AGGREGATE INVARIANT ANALYSIS ``` AGGREGATE: Order ROOT: orders INVARIANT I-001 (CONFIRMED): total_amount = Σ (order_items.quantity × order_items.unit_price) for all order_items WHERE order_id = this.order_id EVIDENCE: Sample data arithmetic: 2 × 124.99 = 249.98 = total_amount ✓ ENFORCEMENT: Application layer only (no DB computed column or CHECK) RISK: Inconsistency possible if items are added/removed without recalculating total_amount INVARIANT I-002 (HYPOTHESIS): COUNT(order_items WHERE order_id = this.order_id) ≥ 1 An Order must contain at least one OrderItem. ENFORCEMENT: None evidenced in schema {D-001} RISK: Empty orders can be persisted INVARIANT I-003 (HYPOTHESIS): order_status transitions are monotonic along a defined workflow (e.g., PENDING → SHIPPED is valid; SHIPPED → PENDING is not) ENFORCEMENT: None evidenced {D-002} RISK: Any status string can be written at any time INVARIANT I-004 (HYPOTHESIS): Σ(payments.amount WHERE order_id = this.order_id) ≤ total_amount Payment total must not exceed order total. ENFORCEMENT: None evidenced; payments has NO amount column {G-009, D-003} RISK: Overpayment undetectable ────────────────────────────────────────────────────────── AGGREGATE: Customer ROOT: customers INVARIANT I-005 (CONFIRMED): email is unique across all Customer instances ENFORCEMENT: DB UNIQUE constraint {E-002} STATUS: Strongly enforced INVARIANT I-006 (CONFIRMED): full_name must be present at registration ENFORCEMENT: NOT NULL constraint STATUS: Enforced INVARIANT I-007 (HYPOTHESIS): A Customer may have zero or more Orders (no minimum) ENFORCEMENT: No constraint requires an order to exist NOTE: Customer can exist without ever ordering — valid e-commerce scenario ``` --- ## ONTOLOGY SUMMARY | Category | Items | |---|---| | **Core Objects** | Customer, Order, OrderItem, Payment, Product (hypothesised) | | **Core Events** | CustomerRegistered, OrderPlaced, OrderStatusChanged, PaymentCaptured, PaymentFailed (hypothesised) | | **States — Order** | SHIPPED (observed); PENDING, CONFIRMED, PROCESSING, DELIVERED, CANCELLED (hypothesised) | | **States — Payment** | Captured (observed); Pending, Authorised, Failed, Refunded (hypothesised) | | **Roles identified** | Customer (buyer), Payment instrument (CreditCard observed) | | **Value Objects identified** | Money (amount+currency), EmailAddress, ProductSKU, OrderStatus, PaymentStatus | | **Constraints explicit (DB-enforced)** | 8 (BR-001 through BR-008) | | **Constraints app-layer only** | 4 (I-001 through I-004) | | **OBDA Gaps requiring expert input** | 10 (G-001 through G-010) | | **DDD Gaps requiring expert input** | 11 (D-001 through D-011) | | **Total open hypotheses** | 11 (H-001 through H-011) | --- > **ANALYST NOTE:** This schema represents a structurally coherent but deliberately minimal e-commerce core. The four tables form a textbook Order Aggregate with a Customer root context. However, the surrounding domain — product catalog, address management, fulfilment, refunds, and audit trails — is entirely absent. The gap registers above (21 combined items) are not criticisms of schema design; they are the scope boundary of what this mapper can assert. Domain expert review against the gap register is the mandated next step before any ontology or knowledge graph build commences.