| 1 |
-- Spread application schema. Applied idempotently on startup.
|
| 2 |
|
| 3 |
CREATE TABLE IF NOT EXISTS subscribers (
|
| 4 |
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
| 5 |
name TEXT NOT NULL,
|
| 6 |
is_internal BOOLEAN NOT NULL DEFAULT FALSE,
|
| 7 |
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
| 8 |
);
|
| 9 |
|
| 10 |
CREATE TABLE IF NOT EXISTS users (
|
| 11 |
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
| 12 |
subscriber_id BIGINT NOT NULL REFERENCES subscribers(id),
|
| 13 |
email TEXT NOT NULL UNIQUE,
|
| 14 |
password_hash TEXT NOT NULL DEFAULT '',
|
| 15 |
name TEXT NOT NULL DEFAULT '',
|
| 16 |
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
| 17 |
);
|
| 18 |
|
| 19 |
CREATE TABLE IF NOT EXISTS sessions (
|
| 20 |
token_hash TEXT PRIMARY KEY,
|
| 21 |
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
| 22 |
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
| 23 |
expires_at TIMESTAMPTZ NOT NULL
|
| 24 |
);
|
| 25 |
|
| 26 |
-- one-time tokens for invites and password resets
|
| 27 |
CREATE TABLE IF NOT EXISTS user_tokens (
|
| 28 |
token_hash TEXT PRIMARY KEY,
|
| 29 |
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
| 30 |
purpose TEXT NOT NULL,
|
| 31 |
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
| 32 |
expires_at TIMESTAMPTZ NOT NULL,
|
| 33 |
used_at TIMESTAMPTZ
|
| 34 |
);
|
| 35 |
|
| 36 |
CREATE TABLE IF NOT EXISTS catalog (
|
| 37 |
part_number TEXT PRIMARY KEY,
|
| 38 |
description TEXT NOT NULL DEFAULT '',
|
| 39 |
spread_price NUMERIC(12,4) NOT NULL,
|
| 40 |
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
| 41 |
);
|
| 42 |
|
| 43 |
CREATE TABLE IF NOT EXISTS boms (
|
| 44 |
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
| 45 |
subscriber_id BIGINT NOT NULL REFERENCES subscribers(id),
|
| 46 |
uploaded_by BIGINT REFERENCES users(id),
|
| 47 |
ref TEXT NOT NULL,
|
| 48 |
filename TEXT NOT NULL DEFAULT '',
|
| 49 |
total_savings NUMERIC(14,2) NOT NULL DEFAULT 0,
|
| 50 |
our_cut NUMERIC(14,2) NOT NULL DEFAULT 0,
|
| 51 |
net_savings NUMERIC(14,2) NOT NULL DEFAULT 0,
|
| 52 |
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
| 53 |
);
|
| 54 |
|
| 55 |
CREATE TABLE IF NOT EXISTS bom_lines (
|
| 56 |
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
| 57 |
bom_id BIGINT NOT NULL REFERENCES boms(id) ON DELETE CASCADE,
|
| 58 |
line INT NOT NULL DEFAULT 0,
|
| 59 |
part_number TEXT NOT NULL DEFAULT '',
|
| 60 |
description TEXT NOT NULL DEFAULT '',
|
| 61 |
quantity NUMERIC NOT NULL DEFAULT 0,
|
| 62 |
unit_price NUMERIC(12,4) NOT NULL DEFAULT 0,
|
| 63 |
spread_price NUMERIC(12,4) NOT NULL DEFAULT 0,
|
| 64 |
line_saving NUMERIC(14,2) NOT NULL DEFAULT 0,
|
| 65 |
matched BOOLEAN NOT NULL DEFAULT FALSE
|
| 66 |
);
|