1 files changed, 39 insertions, 0 deletions
diff --git a/internal/server/api/migrations/20250204150045_last_read_messages.sql b/internal/server/api/migrations/20250204150045_last_read_messages.sql
new file mode 100644
index 0000000..f44436b
--- /dev/null
+++ b/internal/server/api/migrations/20250204150045_last_read_messages.sql
@@ -0,0 +1,39 @@
+-- +goose Up
+CREATE TABLE IF NOT EXISTS last_read_messages (
+  user_id INT NOT NULL REFERENCES users (id),
+  source_id INT NOT NULL, -- frequency_id or receiver_id or sender_id
+  last_read INT NOT NULL,
+  -- can be 0 for "no messages ever read" (null)
+  -- can be between msgs in cases like the message getting deleted
+  PRIMARY KEY (user_id, source_id)
+);
+
+DROP TRIGGER IF EXISTS on_frequency_delete;
+-- +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;
+
+  DELETE FROM last_read_messages WHERE source_id = OLD.id;
+END
+-- +goose StatementEnd
+
+DROP TRIGGER IF EXISTS on_user_delete;
+-- +goose StatementBegin
+CREATE TRIGGER IF NOT EXISTS 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;
+  DELETE FROM last_read_messages WHERE user_id = NEW.id;
+END
+-- +goose StatementEnd
+
+-- +goose Down
+DROP TABLE IF EXISTS last_read_messages;