81 lines
2.6 KiB
SQL
81 lines
2.6 KiB
SQL
-- Migration 003: Create customer hierarchy tables
|
|
-- Customer is the top-level business entity
|
|
-- CustomerAddress and CustomerContact are for customers without accounts
|
|
|
|
-- Top-level business entity
|
|
CREATE TABLE customers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
name VARCHAR(200) NOT NULL UNIQUE,
|
|
status entity_status NOT NULL DEFAULT 'ACTIVE',
|
|
|
|
start_date DATE,
|
|
end_date DATE,
|
|
|
|
-- Billing info
|
|
billing_terms TEXT,
|
|
billing_email VARCHAR(254),
|
|
|
|
-- Wave accounting integration
|
|
wave_customer_id VARCHAR(255)
|
|
);
|
|
|
|
-- Customer addresses (for customers without accounts)
|
|
CREATE TABLE customer_addresses (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(200),
|
|
street_address VARCHAR(255) NOT NULL,
|
|
city VARCHAR(100) NOT NULL,
|
|
state VARCHAR(100) NOT NULL,
|
|
zip_code VARCHAR(20) NOT NULL,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
notes TEXT
|
|
);
|
|
|
|
-- Customer contacts
|
|
CREATE TABLE customer_contacts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
phone VARCHAR(20),
|
|
email VARCHAR(254),
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
notes TEXT
|
|
);
|
|
|
|
-- M2M: CustomerProfile access to Customer (determines data authorization)
|
|
CREATE TABLE customer_profile_access (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
customer_profile_id UUID NOT NULL REFERENCES customer_profiles(id) ON DELETE CASCADE,
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
UNIQUE (customer_profile_id, customer_id)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_customers_status ON customers(status);
|
|
CREATE INDEX idx_customer_addresses_customer ON customer_addresses(customer_id);
|
|
CREATE INDEX idx_customer_contacts_customer ON customer_contacts(customer_id);
|
|
CREATE INDEX idx_customer_profile_access_profile ON customer_profile_access(customer_profile_id);
|
|
CREATE INDEX idx_customer_profile_access_customer ON customer_profile_access(customer_id);
|