# curated.customers

## Purpose

This table stores customer master and contact details in the current curated contract. It provides a reusable warehouse surface for customer lookups, contact enrichment, and linking operational records back to a person or account.

It is a slowly changing business entity compared with order and transaction tables, even if the current contract still carries some legacy KDS-style metadata fields.

## Warehouse design assessment

- `DW fit:` Good candidate for a conformed customer dimension. Free-text contact fields should support analysis, not act as the main integration contract.
- `Cross-client contract:` Keep one canonical customer grain across all tenants and map source-specific records from staging through a stable warehouse key plus source lineage.
- `IDs vs business values:` `guid` and `external_id` are useful for joins, but analysts and AI also need readable attributes such as `customer_display_name`, `customer_status`, and `home_store_name`.
- `AI and business usability:` Agents and business users need customer records that are easy to interpret without decoding operational identifiers or looking back into staging.
- `Recommended extensions:` Add `customer_key`, `source_system`, `source_customer_id`, `customer_status`, `home_store_name`, `created_at_utc`, and `updated_at_utc`.

## Detailed modeling guidance

- `Recommended role:` Use this table as a conformed customer dimension and keep the grain at one row per canonical customer entity.
- `Key differences from a stronger curated model:` The current shape still mixes stable customer master data with operational metadata, so future history handling should be explicit instead of inferred.
- `What is already useful:` The table already contains readable identity and contact attributes, which are valuable for AI and business users.
- `What should move later to serving:` Derived customer segments, churn scores, LTV metrics, and customer-360 summaries belong in serving rather than in this canonical layer.

## Curated contract columns

| Column | Type | Monitor status | Purpose | Monitor rationale |
|---|---|---|---|---|
| `guid` | `VARCHAR(36)` | Available for future monitors | Canonical customer identifier in the curated model. | Could become a stronger identity key for future unified customer monitoring, but current specs rely on loyalty-linked customer numbers instead. Potential monitors: customer-churn-risk, identify-high-value-customers. |
| `external_id` | `VARCHAR(199)` | Available for future monitors | Source-system customer identifier. | Useful for matching with external CRM or loyalty systems if customer identity is unified later. Potential monitors: customer-profiles. |
| `name` | `VARCHAR(65535)` | Available for future monitors | Full customer name. | Adds readable context for future customer-facing outputs, but not needed for scoring. Potential monitors: customer-churn-risk, identify-high-value-customers. |
| `phone` | `VARCHAR(65535)` | Available for future monitors | Primary phone number. | Can support future outreach or identity resolution flows, not current monitor logic. Potential monitors: customer-churn-risk. |
| `phone2` | `VARCHAR(65535)` | Available for future monitors | Secondary phone number. | Same rationale as primary phone, but even less likely to be used directly. Potential monitors: customer-churn-risk. |
| `address` | `VARCHAR(65535)` | Available for future monitors | Primary street address. | Geographic context may matter for later delivery or locality analysis. Potential monitors: segment-customers. |
| `city` | `VARCHAR(65535)` | Available for future monitors | Customer city. | Geographic segmentation could improve future customer clustering. Potential monitors: segment-customers. |
| `state` | `VARCHAR(65535)` | Available for future monitors | Customer state or region. | Regional context may matter for future multi-market comparisons. Potential monitors: segment-customers. |
| `zip` | `VARCHAR(65535)` | Available for future monitors | Postal or ZIP code. | Fine-grained locality can support future trade-area analysis. Potential monitors: segment-customers. |
| `country` | `VARCHAR(65535)` | Available for future monitors | Country name or code. | Useful only if future monitoring spans multiple countries or operational regimes. Potential monitors: segment-customers. |
| `email` | `VARCHAR(65535)` | Available for future monitors | Primary email address. | Can support future retention-action workflows, but not current monitor scoring. Potential monitors: customer-churn-risk. |
| `store_guid` | `VARCHAR(36)` | Available for future monitors | Store or tenant identifier that owns the customer record. | Per-store customer ownership could become useful in future retention analysis. Potential monitors: customer-churn-risk, segment-customers. |
| `create_time` | `INTEGER` | Available for future monitors | Canonical creation timestamp for the customer record. | Can help distinguish new and established customers in future profiling. Potential monitors: customer-profiles. |
| `is_deleted` | `SMALLINT` | Available for future monitors | Soft-delete flag for downstream filtering. | Could protect future customer analysis from stale records, though the table is not currently in active monitor paths. Potential monitors: customer-churn-risk. |
| `customer_key` | `BIGINT` | Recommended extension | Stable warehouse key for the customer entity. | Recommended to create a tenant-independent curated identifier that survives source-system changes and simplifies joins for AI and business users. |
| `source_system` | `VARCHAR(100)` | Recommended extension | Canonical name of the source platform that produced the customer record. | Recommended to preserve lineage when multiple clients use different operational systems such as Brink POS or Logic Control. |
| `source_customer_id` | `VARCHAR(199)` | Recommended extension | Original customer identifier from the source platform. | Recommended to keep traceability back to staging without forcing consumers to query raw source tables. |
| `customer_status` | `VARCHAR(100)` | Recommended extension | Business-friendly lifecycle or activity status of the customer. | Recommended to give AI agents and analysts a readable customer state instead of inferring status from low-level flags. |
| `home_store_name` | `VARCHAR(255)` | Recommended extension | Readable name of the primary store associated with the customer. | Recommended because store names are more useful than only store identifiers in cross-client analysis and reporting. |
| `created_at_utc` | `TIMESTAMP` | Recommended extension | UTC timestamp when the customer record was created in the curated model. | Recommended to provide one standard timestamp field for cross-client temporal analysis. |
| `updated_at_utc` | `TIMESTAMP` | Recommended extension | UTC timestamp when the customer record was last updated in the curated model. | Recommended to support freshness checks and make warehouse timestamps easier to interpret than source-specific integer time fields. |
| `tenant_key` | `BIGINT` | Recommended extension | Stable warehouse key for the tenant, brand, or client that owns the customer. | Recommended because a shared curated schema needs an explicit client-level business key beyond store-level identifiers. |
| `tenant_name` | `VARCHAR(255)` | Recommended extension | Readable tenant, brand, or client name associated with the customer. | Recommended to support cross-client AI and business analysis without forcing joins to separate tenant metadata. |
| `is_current` | `BOOLEAN` | Recommended extension | Canonical flag indicating whether the row is the current customer version. | Recommended if the table evolves toward a slowly changing dimension with preserved customer history. |
| `effective_from_utc` | `TIMESTAMP` | Recommended extension | UTC timestamp from which the current customer version is valid. | Recommended to support explicit dimensional history instead of relying on update timestamps alone. |
| `effective_to_utc` | `TIMESTAMP` | Recommended extension | UTC timestamp until which the current customer version is valid. | Recommended to make historical customer analysis and point-in-time joins possible in the warehouse. |