f8cd37ac8c
Includes: API (Express/TypeScript), Astro site, Python workers, document generators, FCC compliance tools, Canada CRTC formation, Ansible infrastructure, and deployment scripts. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
95 lines
4.4 KiB
PL/PgSQL
95 lines
4.4 KiB
PL/PgSQL
-- Migration 018: Customer portal auth + address/director book
|
|
-- Magic-link authentication — no passwords.
|
|
-- Customers are auto-created on first order; they can later log in with their email.
|
|
|
|
BEGIN;
|
|
|
|
-- ── Customers ─────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id SERIAL PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT,
|
|
phone TEXT,
|
|
company TEXT,
|
|
erpnext_id TEXT, -- ERPNext Customer docname (synced after first order)
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers (email);
|
|
|
|
-- ── Customer sessions (magic-link tokens + JWT refresh) ────────────────────────
|
|
CREATE TABLE IF NOT EXISTS customer_sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL UNIQUE, -- opaque random token (magic link or session)
|
|
token_type TEXT NOT NULL DEFAULT 'magic', -- 'magic' | 'session'
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ, -- magic tokens are single-use
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ip_address TEXT,
|
|
user_agent TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_customer_sessions_token ON customer_sessions (token);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_sessions_customer ON customer_sessions (customer_id);
|
|
|
|
-- ── Saved addresses ───────────────────────────────────────────────────────────
|
|
-- Populated from completed orders. Customers can pick from these on new orders.
|
|
CREATE TABLE IF NOT EXISTS customer_addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
label TEXT, -- e.g. "Home", "Office", auto-generated
|
|
street TEXT NOT NULL,
|
|
street2 TEXT,
|
|
city TEXT NOT NULL,
|
|
province TEXT,
|
|
postal TEXT,
|
|
country TEXT NOT NULL,
|
|
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
|
source_order TEXT, -- order_number this was first seen on
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_customer_addresses_customer ON customer_addresses (customer_id);
|
|
|
|
-- ── Saved directors / contacts ────────────────────────────────────────────────
|
|
-- People who have previously been listed as directors on formation/CRTC orders.
|
|
CREATE TABLE IF NOT EXISTS customer_directors (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
citizenship TEXT,
|
|
address_id INT REFERENCES customer_addresses(id) ON DELETE SET NULL,
|
|
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
|
source_order TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_customer_directors_customer ON customer_directors (customer_id);
|
|
|
|
-- ── Link existing orders to customers (backfill on first login) ───────────────
|
|
-- We don't backfill immediately — on first login we match by email and link.
|
|
ALTER TABLE canada_crtc_orders
|
|
ADD COLUMN IF NOT EXISTS customer_id INT REFERENCES customers(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crtc_orders_customer ON canada_crtc_orders (customer_id);
|
|
|
|
ALTER TABLE orders
|
|
ADD COLUMN IF NOT EXISTS customer_id INT REFERENCES customers(id) ON DELETE SET NULL;
|
|
|
|
-- ── Auto-update updated_at on customers ──────────────────────────────────────
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS customers_updated_at ON customers;
|
|
CREATE TRIGGER customers_updated_at
|
|
BEFORE UPDATE ON customers
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
COMMIT;
|