diff options
Diffstat (limited to 'internal/server/api/migrations')
8 files changed, 123 insertions, 0 deletions
diff --git a/internal/server/api/migrations/20250109134844_users.sql b/internal/server/api/migrations/20250109134844_users.sql new file mode 100644 index 0000000..8ebb9eb --- /dev/null +++ b/internal/server/api/migrations/20250109134844_users.sql @@ -0,0 +1,12 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS users ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + public_key BLOB NOT NULL UNIQUE, + description TEXT NOT NULL DEFAULT '', + is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (false, true)) DEFAULT true, + is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (false, true)) DEFAULT false +); + +-- +goose Down +DROP TABLE IF EXISTS users; diff --git a/internal/server/api/migrations/20250109135310_networks.sql b/internal/server/api/migrations/20250109135310_networks.sql new file mode 100644 index 0000000..c3332c8 --- /dev/null +++ b/internal/server/api/migrations/20250109135310_networks.sql @@ -0,0 +1,13 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS networks ( + id INTEGER PRIMARY KEY, + owner_id INT NOT NULL REFERENCES users (id), + name TEXT NOT NULL, + icon TEXT NOT NULL, + bg_hex_color TEXT NOT NULL, + fg_hex_color TEXT NOT NULL, + is_public BOOLEAN NOT NULL CHECK (is_public IN (false, true)) +); + +-- +goose Down +DROP TABLE IF EXISTS networks; diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql new file mode 100644 index 0000000..a444af1 --- /dev/null +++ b/internal/server/api/migrations/20250109135557_frequencies.sql @@ -0,0 +1,24 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS frequencies ( + id INTEGER PRIMARY KEY, + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + name TEXT NOT NULL, + hex_color TEXT NOT NULL, + perms INT NOT NULL CHECK (perms IN (0, 1, 2)), + -- 0 no access | 1 read | 2 read & write + position INT NOT NULL +); + +-- +goose StatementBegin +CREATE TRIGGER IF NOT EXISTS on_frequency_delete +AFTER DELETE ON frequencies +BEGIN + UPDATE frequencies SET + position = position - 1 + WHERE network_id = OLD.network_id AND position > OLD.position; +END +-- +goose StatementEnd + +-- +goose Down +DROP TABLE IF EXISTS frequencies; +DROP TRIGGER IF EXISTS on_frequency_delete; diff --git a/internal/server/api/migrations/20250109140401_messages.sql b/internal/server/api/migrations/20250109140401_messages.sql new file mode 100644 index 0000000..92f75f4 --- /dev/null +++ b/internal/server/api/migrations/20250109140401_messages.sql @@ -0,0 +1,18 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS messages ( + id INTEGER PRIMARY KEY, + sender_id INT NOT NULL REFERENCES users (id), + content TEXT NOT NULL, + edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false, + + frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE, + receiver_id INT REFERENCES users (id), + + CHECK ( + (frequency_id IS NOT NULL AND receiver_id IS NULL) OR + (frequency_id IS NULL AND receiver_id IS NOT NULL) + ) +); + +-- +goose Down +DROP TABLE IF EXISTS messages; diff --git a/internal/server/api/migrations/20250109141122_members.sql b/internal/server/api/migrations/20250109141122_members.sql new file mode 100644 index 0000000..61fba61 --- /dev/null +++ b/internal/server/api/migrations/20250109141122_members.sql @@ -0,0 +1,15 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS members ( + user_id INT NOT NULL REFERENCES users (id), + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + joined_at TEXT NOT NULL DEFAULT current_timestamp, + is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true, + is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false, + is_muted BOOLEAN NOT NULL CHECK (is_muted IN (false, true)) DEFAULT false, + is_banned BOOLEAN NOT NULL CHECK (is_banned IN (false, true)) DEFAULT false, + ban_reason TEXT, -- null ONLY if is_banned is false + PRIMARY KEY (user_id, network_id) +); + +-- +goose Down +DROP TABLE IF EXISTS members; diff --git a/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql new file mode 100644 index 0000000..8916378 --- /dev/null +++ b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql @@ -0,0 +1,17 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS trusted_users ( + trusting_user_id INT NOT NULL REFERENCES users (id), + trusted_user_id INT NOT NULL REFERENCES users (id), + trusted_public_key BLOB NOT NULL, + PRIMARY KEY (trusting_user_id, trusted_user_id) +); + +CREATE TABLE IF NOT EXISTS blocked_users ( + blocking_user_id INT NOT NULL REFERENCES users (id), + blocked_user_id INT NOT NULL REFERENCES users (id), + PRIMARY KEY (blocking_user_id, blocked_user_id) +); + +-- +goose Down +DROP TABLE IF EXISTS trusted_users; +DROP TABLE IF EXISTS blocked_users; diff --git a/internal/server/api/migrations/20250109143707_on_user_delete.sql b/internal/server/api/migrations/20250109143707_on_user_delete.sql new file mode 100644 index 0000000..940bec4 --- /dev/null +++ b/internal/server/api/migrations/20250109143707_on_user_delete.sql @@ -0,0 +1,15 @@ +-- +goose Up +-- +goose StatementBegin +CREATE TRIGGER on_user_delete +AFTER UPDATE OF is_deleted ON users +WHEN NEW.is_deleted = true +BEGIN + DELETE FROM networks WHERE owner_id = NEW.id; + DELETE FROM members WHERE user_id = NEW.id; + DELETE FROM trusted_users WHERE trusting_user_id = NEW.id OR trusted_user_id = NEW.id; + DELETE FROM blocked_users WHERE blocking_user_id = NEW.id OR blocked_user_id = NEW.id; +END +-- +goose StatementEnd + +-- +goose Down +DROP TRIGGER IF EXISTS on_user_delete; diff --git a/internal/server/api/migrations/20250109144057_indices.sql b/internal/server/api/migrations/20250109144057_indices.sql new file mode 100644 index 0000000..1732fb2 --- /dev/null +++ b/internal/server/api/migrations/20250109144057_indices.sql @@ -0,0 +1,9 @@ +-- +goose Up +CREATE INDEX IF NOT EXISTS idx_frequency_network ON frequencies (network_id); +CREATE INDEX IF NOT EXISTS idx_frequency_messages ON messages (frequency_id); +CREATE INDEX IF NOT EXISTS idx_direct_messages ON messages (sender_id, receiver_id); + +-- +goose Down +DROP INDEX IF EXISTS idx_frequency_network; +DROP INDEX IF EXISTS idx_frequency_messages; +DROP INDEX IF EXISTS idx_direct_messages; |