feat: database schema

1 files changed, 45 insertions, 0 deletions

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..c15b2f2
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,45 @@
+CREATE TABLE IF NOT EXISTS servers (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    host TEXT NOT NULL UNIQUE,
+    port INTEGER NOT NULL,
+    nick TEXT NOT NULL,
+    pass TEXT NOT NULL,
+    capabilities TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS rooms (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
+    `name` TEXT NOT NULL UNIQUE,
+    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    departed_at TIMESTAMP,
+    INDEX (`name`),
+    INDEX (id, server_id)
+);
+
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
+    nick TEXT NOT NULL,
+    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    departed_at TIMESTAMP,
+    INDEX (nick),
+    INDEX (server_id)
+);
+
+CREATE TABLE IF NOT EXISTS messages (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    command VARCHAR(32) NOT NULL,
+    
+    params TEXT,
+    tags TEXT,
+
+    sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
+    INDEX (room_id),
+    INDEX (user_id),
+    INDEX (command),
+    INDEX (room_id, sent_at),
+    FULLTEXT INDEX ft_message (params)
+);
\ No newline at end of file