# curated.loyalty_customers

## Purpose

This table stores customer records specific to a loyalty program rather than general POS identity. It is intended for balance checks, account linking, and customer engagement or reward workflows.

The table should be treated carefully because it mixes business identity fields with credential-oriented legacy columns from the current contract.

## 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 |
|---|---|---|---|---|
| `customer_number` | `VARCHAR(50)` | No | Customer number used by the loyalty system. |  |
| `id` | `INTEGER` | No | Loyalty customer identifier. |  |
| `first_name` | `VARCHAR(100)` | No | Customer first name. |  |
| `last_name` | `VARCHAR(100)` | No | Customer last name. |  |
| `company` | `VARCHAR(150)` | Yes | Company associated with the loyalty customer. |  |
| `points_balance` | `INTEGER` | Yes | Current loyalty points balance. |  |
| `program_name` | `VARCHAR(255)` | Yes | Readable name of the loyalty program. |  |
| `member_status` | `VARCHAR(100)` | Yes | Business-friendly loyalty member status such as active, suspended, or inactive. |  |
| `tier_name` | `VARCHAR(100)` | Yes | Readable loyalty tier name. |  |
| `enrolled_at_utc` | `TIMESTAMP` | Yes | UTC timestamp when the customer enrolled in the loyalty program. |  |
| `last_activity_at_utc` | `TIMESTAMP` | Yes | UTC timestamp of the customer's last loyalty-related activity. |  |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `loyalty_customer_key` | `BIGINT` | Yes | Stable warehouse key for the loyalty customer entity. |  |
| `source_system` | `VARCHAR(100)` | Yes | Canonical name of the source platform that produced the loyalty record. |  |
| `source_loyalty_customer_id` | `VARCHAR(199)` | Yes | Original loyalty customer identifier from the source platform. |  |
| `tenant_key` | `BIGINT` | Yes | Stable warehouse key for the tenant, brand, or client that owns the loyalty program. |  |
| `tenant_name` | `VARCHAR(255)` | Yes | Readable tenant, brand, or client name associated with the loyalty member. |  |
| `is_current` | `BOOLEAN` | Yes | Canonical flag indicating whether the row is the current loyalty-member version. |  |
| `effective_from_utc` | `TIMESTAMP` | Yes | UTC timestamp from which the current loyalty-member version is valid. |  |
| `effective_to_utc` | `TIMESTAMP` | Yes | UTC timestamp until which the current loyalty-member 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. |