72 lines
2.4 KiB
SQL
72 lines
2.4 KiB
SQL
-- +goose Up
|
|
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
oidc_subject TEXT UNIQUE NOT NULL,
|
|
username TEXT NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE sites (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
domain TEXT UNIQUE NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE payments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
amount INTEGER NOT NULL, -- Store amount in cents to avoid floating point issues
|
|
currency TEXT NOT NULL DEFAULT 'USD',
|
|
status TEXT NOT NULL, -- e.g., 'succeeded', 'pending', 'failed'
|
|
payment_processor_id TEXT UNIQUE, -- ID from your payment processor (e.g., Stripe Payment Intent ID)
|
|
paid_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_sites_user_id ON sites(user_id);
|
|
CREATE INDEX idx_payments_user_id ON payments(user_id);
|
|
CREATE INDEX idx_users_oidc_subject ON users(oidc_subject);
|
|
CREATE INDEX idx_sites_domain ON sites(domain);
|
|
CREATE INDEX idx_payments_payment_processor_id ON payments(payment_processor_id);
|
|
|
|
CREATE TRIGGER trigger_users_updated_at
|
|
AFTER UPDATE ON users
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
|
|
END;
|
|
|
|
CREATE TRIGGER trigger_sites_updated_at
|
|
AFTER UPDATE ON sites
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE sites SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
|
|
END;
|
|
|
|
CREATE TRIGGER trigger_payments_updated_at
|
|
AFTER UPDATE ON payments
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE payments SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
|
|
END;
|
|
|
|
-- +goose Down
|
|
DROP TRIGGER IF EXISTS trigger_payments_updated_at;
|
|
DROP TRIGGER IF EXISTS trigger_sites_updated_at;
|
|
DROP TRIGGER IF EXISTS trigger_users_updated_at;
|
|
DROP INDEX IF EXISTS idx_payments_payment_processor_id;
|
|
DROP INDEX IF EXISTS idx_sites_domain;
|
|
DROP INDEX IF EXISTS idx_users_oidc_subject;
|
|
DROP INDEX IF EXISTS idx_payments_user_id;
|
|
DROP INDEX IF EXISTS idx_sites_user_id;
|
|
DROP TABLE IF EXISTS payments;
|
|
DROP TABLE IF EXISTS sites;
|
|
DROP TABLE IF EXISTS users;
|