{EXECUTIVE_SUMMARY} - World A facts (DDL): 12 - World A facts (Catalog): 2 - World B inferences: 4 - Gaps: 6 - Weakly-named columns flagged: 2 - World C claims: 1 supported / 1 demoted to Gap - Catalog conflicts (DDL vs engine): 0 - Provenance integrity: PASS - Top risk: customers.status has no catalog-enforced constraint and no semantic definition — account validity logic central to the World C claim cannot be verified from schema alone. --- {WORLD_A_FACTS} **DDL-sourced (syntactic):** - Source: customers.id | Fact: INT, PRIMARY KEY | {World-A: ddl} - Source: customers.email | Fact: VARCHAR(255), no NOT NULL, no UNIQUE constraint declared | {World-A: ddl} - Source: customers.status | Fact: VARCHAR(20), no NOT NULL, no CHECK constraint declared | {World-A: ddl} - Source: orders.id | Fact: INT, PRIMARY KEY | {World-A: ddl} - Source: orders.customer_id | Fact: INT, no NOT NULL declared | {World-A: ddl} - Source: orders.order_status | Fact: VARCHAR(30) | {World-A: ddl} - Source: orders.total_amount | Fact: DECIMAL(10,2), no NOT NULL declared | {World-A: ddl} - Source: orders.created_at | Fact: DATETIME, no NOT NULL, no DEFAULT declared | {World-A: ddl} - Source: orders (table-level) | Fact: CONSTRAINT FK_Orders_Customers — FOREIGN KEY (customer_id) REFERENCES customers(id) | {World-A: ddl} - Source: SELECT query | Fact: joins orders to customers on o.customer_id = c.id | {World-A: ddl} - Source: SELECT query | Fact: projects o.id, o.order_status, c.email | {World-A: ddl} - Source: SELECT query | Fact: JOIN type is INNER JOIN (implicit — keyword JOIN without OUTER/LEFT) | {World-A: ddl} **Catalog-sourced (engine-enforced):** - Source: FK_Orders_Customers | Fact: FK enforced by engine — orders.customer_id → customers.id | {World-A: catalog} - Source: CK_OrderStatus | Fact: CHECK constraint enforced by engine on orders.order_status — permitted values are 'pending', 'paid', 'cancelled' exactly | {World-A: catalog} **Catalog conflicts (DDL ≠ engine):** - None detected. FK_Orders_Customers appears in both DDL and catalog consistently. CK_OrderStatus is present in catalog but absent from DDL (not a conflict — it is a catalog addition; logged as a structural gap below). --- {WORLD_B_INFERENCES} - Inference: orders is a dependent entity; a row in orders cannot reference a non-existent customers.id at the time of insert or update. Derives from: FK_Orders_Customers {World-A: catalog} Confidence: MEDIUM (decay -1) - Inference: The orders.order_status column participates in a finite state machine with exactly three named states: 'pending', 'paid', 'cancelled'. Derives from: CK_OrderStatus {World-A: catalog} Confidence: MEDIUM (decay -1) - Inference: The SELECT query will silently exclude orders whose customer_id is NULL (due to INNER JOIN semantics), even though customer_id is not declared NOT NULL in DDL. Derives from: orders.customer_id no NOT NULL {World-A: ddl} + INNER JOIN {World-A: ddl} Confidence: MEDIUM (decay -1) - Inference: customers functions as a reference/lookup table relative to orders — it is the referenced side of the only FK relationship in the schema. Derives from: FK_Orders_Customers {World-A: catalog} Confidence: MEDIUM (decay -1) *(No LOW-confidence compound inferences generated; all are single-step A→B. None suppressed by CONFIDENCE_THRESHOLD: medium.)* --- {WEAKLY_NAMED_COLUMNS} - Column: status | Table: customers Hypothesis: "status likely represents the active/inactive or account lifecycle state of a customer" Gap: "Column name lacks semantic specificity to confirm — no CHECK constraint, no enum, no catalog definition constrains its values" Impact: HIGH - Column: order_status | Table: orders Hypothesis: "order_status likely represents the fulfillment or payment lifecycle state of an order" Gap: "Column name carries moderate domain signal, but meaning is confirmed only to the three engine-enforced values ('pending', 'paid', 'cancelled') — transitions, terminal states, and business rules beyond value enumeration are unconfirmed" Impact: HIGH --- {WORLD_C_RECONCILIATION} - Claim from context: "Orders cannot exist without a valid customer account." Status: PARTIALLY SUPPORTED Reasoning: FK_Orders_Customers {World-A: catalog} enforces referential integrity — orders.customer_id must match an existing customers.id. However, "valid customer account" implies a status condition on the customers row (e.g., status = 'active'), which has no DDL or catalog enforcement. The FK prevents orphaned orders; it does not prevent orders against suspended or deleted customer records. - Claim from context: "Cancelled orders should never be shipped." Status: DEMOTED_TO_GAP Reasoning: The schema enforces that 'cancelled' is a legal value for order_status {World-A: catalog}, but contains no constraint, trigger reference, or column (e.g., shipped_at, shipment_status) that would enforce or record shipping prevention. The business rule exists in World C only. --- {GAPS} - {GAP-1} Description: CK_OrderStatus is engine-enforced per catalog but absent from the provided DDL CREATE TABLE statement. Source: STRUCTURAL Impact: MEDIUM Questions: - Q1: Was CK_OrderStatus added via ALTER TABLE after the original CREATE TABLE? If so, the DDL snippet is incomplete — what is the full migration history? - Q2: Should this constraint be represented in the canonical DDL to ensure reproducibility in deployments? - {GAP-2} Description: customers.status has no CHECK constraint, no NOT NULL, and no catalog-enforced definition. Its permitted values, nullability, and meaning are entirely undefined in World A. Source: WEAKLY_NAMED Impact: HIGH Questions: - Q1: What are the legal values for customers.status (e.g., 'active', 'suspended', 'deleted')? Is a CHECK constraint or lookup table enforcing this? - Q2: Can a customer with a non-active status still receive new orders? If not, where is that rule enforced? - {GAP-3} Description: orders.customer_id has no NOT NULL constraint in DDL. A NULL customer_id would pass DDL validation while silently violating referential intent and being excluded from the SELECT query's INNER JOIN results. Source: STRUCTURAL Impact: HIGH Questions: - Q1: Should orders.customer_id be NOT NULL? Is there a legitimate business case for an order with no associated customer? - Q2: Are there existing rows in the orders table where customer_id IS NULL? This would represent data invisible to the current SELECT query. - {GAP-4} Description: The "cancelled orders should never be shipped" rule (World C) has no schema-level enforcement mechanism. No shipment column, no trigger, no additional constraint exists in World A to prevent or record this. Source: WORLD_C_DEMOTION Impact: HIGH Questions: - Q1: Is shipping prevention enforced at the application layer only? If so, what happens if data is written directly to the database bypassing the application? - Q2: Is there a separate shipments table or a shipped_at/shipment_status column planned or existing elsewhere in the schema that was not included in the snippet? - {GAP-5} Description: The "valid customer account" qualifier in the World C claim is unsupported. The FK ensures the customer row exists; it does not enforce that the customer is in any particular status state. Source: WORLD_C_DEMOTION Impact: HIGH Questions: - Q1: Is order creation gated on customers.status at the application layer? Is there a CHECK constraint or trigger in the full schema that enforces this? - Q2: What status value, if any, should disqualify a customer from having new orders created against their account? - {GAP-6} Description: customers.email has no UNIQUE constraint and no NOT NULL constraint in DDL. Duplicate or null emails are structurally permitted. Source: STRUCTURAL Impact: MEDIUM Questions: - Q1: Is email intended to be a unique business identifier for customers? If so, why is the UNIQUE constraint absent? - Q2: Does a separate unique index exist in the engine (not captured in this catalog snippet)? The provided catalog queries did not include sys.indexes output. --- {NEXT_ACTIONS} - Before extending this schema: close GAP-2 (customers.status value domain) and GAP-3 (customer_id nullability) — these are the highest-leverage structural ambiguities and both directly undermine the World C claim about order validity. - Before reporting against this database: confirm the demoted assumption in GAP-5 — "valid customer account" must be operationally defined in World A terms before any query filtering on customer validity can be trusted. - Catalog completeness: re-run catalog queries to include sys.indexes output for the customers table — email uniqueness (GAP-6) cannot be confirmed without it. - Highest-leverage clarification: **What are the legal values and enforcement mechanism for customers.status, and does a non-active status currently block order creation at any layer of the system?**