feat: save words into the database

1 files changed, 32 insertions, 0 deletions

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..e453239
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,32 @@
+CREATE TABLE IF NOT EXISTS channels (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS users (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    name TEXT NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS channel_words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
+    usage_count BIGINT NOT NULL DEFAULT 0,
+    first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+    last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+
+    UNIQUE (channel_id, user_id, word_id)
+);
\ No newline at end of file