129 lines
4.5 KiB
JavaScript
129 lines
4.5 KiB
JavaScript
exports.up = (pgm) => {
|
|
pgm.sql(`
|
|
CREATE TABLE IF NOT EXISTS items (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
price_cents INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'available',
|
|
reserved_until TIMESTAMPTZ,
|
|
sold_at TIMESTAMPTZ,
|
|
paypal_order_id TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS item_images (
|
|
id SERIAL PRIMARY KEY,
|
|
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
image_path TEXT NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id SERIAL PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
name TEXT,
|
|
email_verified BOOLEAN NOT NULL DEFAULT false,
|
|
marketing_consent BOOLEAN NOT NULL DEFAULT false,
|
|
marketing_consent_at TIMESTAMPTZ,
|
|
marketing_consent_text TEXT,
|
|
unsubscribe_token TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customer_sessions (
|
|
token TEXT PRIMARY KEY,
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customer_tokens (
|
|
token TEXT PRIMARY KEY,
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
INSERT INTO admin_settings (key, value) VALUES ('cart_expiry_hours', '24') ON CONFLICT (key) DO NOTHING;
|
|
|
|
CREATE TABLE IF NOT EXISTS carts (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INTEGER NOT NULL UNIQUE REFERENCES customers(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cart_items (
|
|
id SERIAL PRIMARY KEY,
|
|
cart_id INTEGER NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL UNIQUE REFERENCES items(id) ON DELETE CASCADE,
|
|
added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
last_reminder_sent_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS shipping_addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
full_name TEXT NOT NULL,
|
|
address_line1 TEXT NOT NULL,
|
|
address_line2 TEXT,
|
|
city TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
postal_code TEXT NOT NULL,
|
|
country TEXT NOT NULL DEFAULT 'US',
|
|
is_default BOOLEAN NOT NULL DEFAULT false,
|
|
usps_validated BOOLEAN NOT NULL DEFAULT false,
|
|
usps_standardized JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS checkouts (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
|
|
shipping_address_id INTEGER REFERENCES shipping_addresses(id) ON DELETE SET NULL,
|
|
processor TEXT NOT NULL,
|
|
processor_order_id TEXT,
|
|
amount_cents INTEGER,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
raw_event JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS checkout_items (
|
|
checkout_id INTEGER NOT NULL REFERENCES checkouts(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
price_cents INTEGER NOT NULL,
|
|
PRIMARY KEY (checkout_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id SERIAL PRIMARY KEY,
|
|
item_id INTEGER REFERENCES items(id),
|
|
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
|
|
checkout_id INTEGER REFERENCES checkouts(id) ON DELETE SET NULL,
|
|
processor TEXT NOT NULL,
|
|
processor_order_id TEXT,
|
|
amount_cents INTEGER,
|
|
status TEXT,
|
|
raw_event JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
`);
|
|
};
|
|
|
|
// Deliberately no destructive down migration for the baseline — reverting this
|
|
// would drop every table and all data. If you ever need to roll back past this
|
|
// point, do it manually and deliberately, not via `migrate:down`.
|
|
exports.down = (pgm) => {
|
|
pgm.sql(`SELECT 1;`);
|
|
}; |