# 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.

## Schema contract

The deployed SQL under `backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/` is the source of truth for the current column names, types, and nullability.

## Column groups

- Business properties describe the business entity, event, fact, or reference record represented by the table.
- Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, slowly changing dimension controls, and ingestion metadata.

## Business properties

| Column | Type | Nullable | Business Meaning | Notes |
|---|---|---|---|---|
| `guid` | `VARCHAR(36)` | No | Canonical customer identifier in the curated model. |  |
| `external_id` | `VARCHAR(199)` | No | Source-system customer identifier. |  |
| `name` | `VARCHAR(65535)` | Yes | Full customer name. |  |
| `phone` | `VARCHAR(65535)` | Yes | Primary phone number. |  |
| `phone2` | `VARCHAR(65535)` | Yes | Secondary phone number. |  |
| `address` | `VARCHAR(65535)` | Yes | Primary street address. |  |
| `city` | `VARCHAR(65535)` | Yes | Customer city. |  |
| `state` | `VARCHAR(65535)` | Yes | Customer state or region. |  |
| `zip` | `VARCHAR(65535)` | Yes | Postal or ZIP code. |  |
| `country` | `VARCHAR(65535)` | Yes | Country name or code. |  |
| `email` | `VARCHAR(65535)` | Yes | Primary email address. |  |
| `store_guid` | `VARCHAR(36)` | No | Store or tenant identifier that owns the customer record. |  |
| `create_time` | `INTEGER` | No | Canonical creation timestamp for the customer record. |  |
| `is_deleted` | `SMALLINT` | No | Soft-delete flag for downstream filtering. |  |
| `customer_status` | `VARCHAR(100)` | Yes | Business-friendly lifecycle or activity status of the customer. |  |
| `home_store_name` | `VARCHAR(255)` | Yes | Readable name of the primary store associated with the customer. |  |
| `created_at_utc` | `TIMESTAMP` | Yes | UTC timestamp when the customer record was created in the curated model. |  |
| `updated_at_utc` | `TIMESTAMP` | Yes | UTC timestamp when the customer record was last updated in the curated model. |  |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `customer_key` | `BIGINT` | Yes | Stable warehouse key for the customer entity. |  |
| `source_system` | `VARCHAR(100)` | Yes | Canonical name of the source platform that produced the customer record. |  |
| `source_customer_id` | `VARCHAR(199)` | Yes | Original customer identifier from the source platform. |  |
| `tenant_key` | `BIGINT` | Yes | Stable warehouse key for the tenant, brand, or client that owns the customer. |  |
| `tenant_name` | `VARCHAR(255)` | Yes | Readable tenant, brand, or client name associated with the customer. |  |
| `is_current` | `BOOLEAN` | Yes | Canonical flag indicating whether the row is the current customer version. |  |
| `effective_from_utc` | `TIMESTAMP` | Yes | UTC timestamp from which the current customer version is valid. |  |
| `effective_to_utc` | `TIMESTAMP` | Yes | UTC timestamp until which the current customer version is valid. |  |
| `_etl_batch_id` | `VARCHAR(256)` | Yes | ETL batch lineage identifier. |  |
| `_etl_source_type` | `VARCHAR(100)` | Yes | Source adapter type used by ETL. |  |
| `_etl_ingested_at` | `TIMESTAMP` | No | Warehouse ingestion timestamp for the curated row. | Required ingestion audit timestamp. |