Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.

branches/life-cycle/warehouse-model/docs/features/ETL/warehouse-data/curated-model/customers.md 8,285 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.