Customers
Primary customer identity, status, consent, and account activity.
| Field | Type | Description |
|---|
| customer_id | UUID / BIGINT | Primary unique identifier |
| customer_number | VARCHAR | Human-readable customer number |
| first_name, middle_name, last_name, full_name | VARCHAR | Customer names |
| date_of_birth, gender, profile_photo_url | DATE / VARCHAR / TEXT | Profile attributes |
| email, phone_mobile, phone_home, phone_work | VARCHAR | Contact channels |
| preferred_language, preferred_currency | VARCHAR | Localization preferences |
| customer_status, customer_type | VARCHAR | Lifecycle and segment classification |
| account_created_at, last_login_at, last_activity_at | TIMESTAMP | Account activity timestamps |
| marketing_opt_in, sms_opt_in, email_verified, phone_verified | BOOLEAN | Consent and verification flags |
| created_by, updated_at | UUID / TIMESTAMP | Administrative ownership and updates |
Customer_Addresses
Multiple billing, shipping, home, and geospatial addresses per customer.
| Field | Type | Description |
|---|
| address_id, customer_id | UUID | Primary key and customer reference |
| address_type | VARCHAR | Billing, shipping, home |
| address_line_1, address_line_2 | VARCHAR | Street and unit details |
| city, state_province, postal_code, country | VARCHAR | Location details |
| latitude, longitude | DECIMAL | GPS coordinates |
| is_primary, created_at | BOOLEAN / TIMESTAMP | Primary flag and creation time |
Customer_Authentication
Credentials, MFA, device/session data, lockouts, and reset history.
| Field | Type | Description |
|---|
| auth_id, customer_id | UUID | Primary key and customer reference |
| username, password_hash, password_salt | VARCHAR / TEXT | Login credentials |
| password_last_changed, last_password_reset | TIMESTAMP | Password lifecycle |
| mfa_enabled, mfa_secret | BOOLEAN / TEXT | Multi-factor configuration |
| failed_login_attempts, account_locked | INTEGER / BOOLEAN | Lockout controls |
| last_login_ip, device_fingerprint | VARCHAR / TEXT | Security context |
| session_token, refresh_token | TEXT | Active session credentials |
Customer_Preferences
Personalization, notification, communication, and accessibility settings.
| Field | Type | Description |
|---|
| preference_id, customer_id | UUID | Primary key and customer reference |
| theme_preference | VARCHAR | Dark or light mode |
| notification_preferences | JSON | Notification settings |
| communication_channel | VARCHAR | Preferred contact channel |
| interests, personalization_data, accessibility_settings | JSON | Interests, AI data, and accessibility options |
Orders & Order_Items
Purchases, status, pricing, tax, shipping, discounts, and line items.
| Field | Type | Description |
|---|
| order_id, customer_id, order_number | UUID / VARCHAR | Order identity and customer reference |
| order_status, payment_status, payment_method | VARCHAR | Order and payment state |
| order_total, tax_amount, shipping_amount, discount_amount | DECIMAL | Financial totals |
| billing_address_id, shipping_address_id | UUID | Address references |
| order_created_at, fulfilled_at | TIMESTAMP | Order lifecycle timestamps |
| order_item_id, product_id, sku, product_name | UUID / VARCHAR | Line item identity |
| quantity, unit_price, total_price, tax_amount | INTEGER / DECIMAL | Line item quantity and pricing |
Payments
Financial transactions, providers, gateway IDs, ACH, invoices, and status.
| Field | Type | Description |
|---|
| payment_id, customer_id, order_id | UUID | Payment, customer, and order references |
| transaction_id, payment_provider | VARCHAR | Gateway transaction tracking |
| payment_method, payment_status | VARCHAR | Method and lifecycle state |
| amount, currency | DECIMAL / VARCHAR | Payment value |
| authorization_code, ach_trace_number, invoice_number | VARCHAR | Banking and invoice references |
| payment_date | TIMESTAMP | Payment timestamp |
Support_Tickets
Customer service cases, assignment, priority, resolution, and timing.
| Field | Type | Description |
|---|
| ticket_id, customer_id | UUID | Ticket and customer references |
| subject, description | VARCHAR / TEXT | Issue summary and detail |
| ticket_status, priority | VARCHAR | Status and urgency |
| assigned_agent | UUID | Support representative |
| resolution_notes | TEXT | Resolution details |
| created_at, resolved_at | TIMESTAMP | Support lifecycle timestamps |
Customer_Communications
Email, SMS, phone, message body, direction, status, and timestamp history.
| Field | Type | Description |
|---|
| communication_id, customer_id | UUID | Communication and customer references |
| communication_type, direction | VARCHAR | Email, SMS, phone; inbound or outbound |
| subject, message_body | VARCHAR / TEXT | Content and message body |
| communication_status, sent_at | VARCHAR / TIMESTAMP | Delivery status and timestamp |
Customer_Segments
Marketing and CRM segmentation rules plus customer memberships.
| Field | Type | Description |
|---|
| segment_id, segment_name | UUID / VARCHAR | Segment identity and name |
| description, rules_json | TEXT / JSON | Segment purpose and logic |
| membership_id, customer_id, segment_id | UUID | Membership record and references |
| joined_at | TIMESTAMP | Segment join timestamp |
Customer_Activity_Log
Behavioral analytics, sessions, devices, browsers, operating systems, and IPs.
| Field | Type | Description |
|---|
| activity_id, customer_id | UUID | Activity and customer references |
| activity_type, page_url | VARCHAR / TEXT | Action and page visited |
| device_type, browser, operating_system | VARCHAR | Device and software context |
| ip_address, session_id | VARCHAR | Network and session references |
| activity_timestamp | TIMESTAMP | Event timestamp |
Subscriptions & Loyalty_Accounts
Recurring memberships, billing cycles, cancellations, points, tiers, and rewards.
| Field | Type | Description |
|---|
| subscription_id, customer_id, plan_name | UUID / VARCHAR | Subscription identity and plan |
| billing_cycle, subscription_status | VARCHAR | Monthly/yearly and active/cancelled state |
| start_date, renewal_date, cancellation_date | DATE | Subscription lifecycle dates |
| loyalty_id, points_balance, tier_level | UUID / INTEGER / VARCHAR | Loyalty identity, points, and tier |
| lifetime_points, last_activity | INTEGER / TIMESTAMP | Total rewards and latest activity |
Audit_Log & AI_Customer_Profile
Compliance audit trails plus AI-driven churn, LTV, sentiment, and recommendations.
| Field | Type | Description |
|---|
| audit_id, entity_name, entity_id | UUID / VARCHAR | Changed entity reference |
| action_type, old_values, new_values | VARCHAR / JSON | Insert, update, delete and before/after values |
| changed_by, changed_at | UUID / TIMESTAMP | Actor and change timestamp |
| ai_profile_id, customer_id | UUID | AI profile and customer reference |
| churn_risk_score, lifetime_value_score, sentiment_score, engagement_score | DECIMAL | Predictive model scores |
| recommendation_profile, behavioral_cluster | JSON / VARCHAR | Recommendation data and AI cluster |