commit d40ad033553d3a3f68d8c07e699ff3d4f94b633b parent 21766c95356e4b92c78213a399dec16fd52c31be Author: Silas Brack <silasbrack@gmail.com> Date: Sun, 28 Dec 2025 17:37:33 +0100 Add backend Diffstat:
25 files changed, 456 insertions(+), 0 deletions(-)
diff --git a/.fdignore b/.fdignore @@ -0,0 +1,3 @@ +*.db* +*.wasm + diff --git a/traildepot/.gitignore b/traildepot/.gitignore @@ -0,0 +1,16 @@ +# Deployment-specific directories: +backups/ +data/ +secrets/ +uploads/ +wasm/ +scripts/ + +metadata.textproto + +# Runtime files, will be overriden by `trail`. +trailbase.d.ts +trailbase.js + +# Any potential MaxMind GeoIP dbs. +*.mmdb diff --git a/traildepot/config.textproto b/traildepot/config.textproto @@ -0,0 +1,41 @@ +# Auto-generated config.Config textproto +email {} +server { + application_name: "TrailBase" + logs_retention_sec: 604800 +} +auth { + auth_token_ttl_sec: 3600 + refresh_token_ttl_sec: 2592000 +} +jobs {} +record_apis: [{ + name: "chat" + table_name: "chat" + acl_world: [READ, CREATE] +}, { + name: "message" + table_name: "message" + acl_world: [READ, CREATE, UPDATE, DELETE] + enable_subscriptions: true + create_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _REQ_.chat_id AND cp.user_id = _USER_.id )" + read_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.chat_id AND cp.user_id = _USER_.id )" + update_access_rule: "EXISTS(SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.chat_id AND cp.user_id = _USER_.id AND _ROW_.sender_id = _USER_.id)" + delete_access_rule: "EXISTS(SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.chat_id AND cp.user_id = _USER_.id AND _ROW_.sender_id = _USER_.id)" +}, { + name: "profile" + table_name: "profile" + acl_world: [CREATE, READ] +}, { + name: "user_chats" + table_name: "user_chats" + acl_authenticated: [READ] + read_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.id AND cp.user_id = _USER_.id )" +}, { + name: "chat_participation" + table_name: "chat_participation" + acl_world: [CREATE, READ, DELETE] + create_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _REQ_.chat_id AND cp.user_id = _USER_.id )" + read_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.chat_id AND cp.user_id = _USER_.id )" + delete_access_rule: "EXISTS( SELECT 1 FROM chat_participation AS cp WHERE cp.chat_id = _ROW_.chat_id AND cp.user_id = _USER_.id )" +}] +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766662985__create_table_user.sql b/traildepot/migrations/main/U1766662985__create_table_user.sql @@ -0,0 +1,5 @@ +CREATE TABLE "profile" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + '_user_id' BLOB NOT NULL REFERENCES '_user'('id') UNIQUE, + 'name' TEXT NOT NULL +) STRICT; diff --git a/traildepot/migrations/main/U1766663030__create_table_chat.sql b/traildepot/migrations/main/U1766663030__create_table_chat.sql @@ -0,0 +1 @@ +CREATE TABLE "chat" ('id' INTEGER PRIMARY KEY NOT NULL) STRICT; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766663059__create_table_chat_participation.sql b/traildepot/migrations/main/U1766663059__create_table_chat_participation.sql @@ -0,0 +1,4 @@ +CREATE TABLE "chat_participation" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id') +) STRICT; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766663112__create_table_message.sql b/traildepot/migrations/main/U1766663112__create_table_message.sql @@ -0,0 +1,5 @@ +CREATE TABLE "message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL +) STRICT; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766663148__alter_table_chat_participation.sql b/traildepot/migrations/main/U1766663148__alter_table_chat_participation.sql @@ -0,0 +1,26 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat_participation" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'sender_id' INTEGER NOT NULL REFERENCES 'profile'('id') +) STRICT; + +INSERT INTO + "__alter_table_chat_participation" ("id", "chat_id") +SELECT + "id", + "chat_id" +FROM + "chat_participation"; + +DROP TABLE "chat_participation"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat_participation" RENAME TO "chat_participation"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766663163__alter_table_chat_participation.sql b/traildepot/migrations/main/U1766663163__alter_table_chat_participation.sql @@ -0,0 +1,27 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat_participation" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'profile_id' INTEGER NOT NULL REFERENCES 'profile'('id') +) STRICT; + +INSERT INTO + "__alter_table_chat_participation" ("id", "chat_id", "profile_id") +SELECT + "id", + "chat_id", + "sender_id" +FROM + "chat_participation"; + +DROP TABLE "chat_participation"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat_participation" RENAME TO "chat_participation"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766663201__alter_table_message.sql b/traildepot/migrations/main/U1766663201__alter_table_message.sql @@ -0,0 +1,28 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL, + 'sender_id' INTEGER NOT NULL REFERENCES 'profile'('id') +) STRICT; + +INSERT INTO + "__alter_table_message" ("content", "id", "chat_id") +SELECT + "content", + "id", + "chat_id" +FROM + "message"; + +DROP TABLE "message"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_message" RENAME TO "message"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766664436__alter_table_message.sql b/traildepot/migrations/main/U1766664436__alter_table_message.sql @@ -0,0 +1,29 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL, + 'sender_id' INTEGER REFERENCES 'profile'('id') NULL +) STRICT; + +INSERT INTO + "__alter_table_message" ("content", "id", "chat_id", "sender_id") +SELECT + "content", + "id", + "chat_id", + "sender_id" +FROM + "message"; + +DROP TABLE "message"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_message" RENAME TO "message"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766664610__alter_table_message.sql b/traildepot/migrations/main/U1766664610__alter_table_message.sql @@ -0,0 +1,30 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL, + 'sender_id' INTEGER REFERENCES 'profile'('id') NULL, + 'sent_at' TEXT NOT NULL +) STRICT; + +INSERT INTO + "__alter_table_message" ("sender_id", "chat_id", "id", "content") +SELECT + "sender_id", + "chat_id", + "id", + "content" +FROM + "message"; + +DROP TABLE "message"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_message" RENAME TO "message"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766666708__alter_table_message.sql b/traildepot/migrations/main/U1766666708__alter_table_message.sql @@ -0,0 +1,37 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL, + 'sender_id' INTEGER NULL REFERENCES 'profile'('id'), + 'sent_at' TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP +) STRICT; + +INSERT INTO + "__alter_table_message" ( + "content", + "chat_id", + "sender_id", + "id", + "sent_at" + ) +SELECT + "content", + "chat_id", + "sender_id", + "id", + "sent_at" +FROM + "message"; + +DROP TABLE "message"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_message" RENAME TO "message"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766701521__update.sql b/traildepot/migrations/main/U1766701521__update.sql @@ -0,0 +1,5 @@ +CREATE VIEW user_profile AS +SELECT P.*, U.email +FROM _user AS U +INNER JOIN profile AS P + ON U.id = P._user_id; diff --git a/traildepot/migrations/main/U1766702480__alter_table_chat.sql b/traildepot/migrations/main/U1766702480__alter_table_chat.sql @@ -0,0 +1,24 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'title' TEXT NOT NULL +) STRICT; + +INSERT INTO + "__alter_table_chat" ("id") +SELECT + "id" +FROM + "chat"; + +DROP TABLE "chat"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat" RENAME TO "chat"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766752610__alter_table_chat.sql b/traildepot/migrations/main/U1766752610__alter_table_chat.sql @@ -0,0 +1,27 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'title' TEXT NOT NULL, + 'created_at' TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, + 'created_by' BLOB NOT NULL REFERENCES '_user'('id') +) STRICT; + +INSERT INTO + "__alter_table_chat" ("id", "title") +SELECT + "id", + "title" +FROM + "chat"; + +DROP TABLE "chat"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat" RENAME TO "chat"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; diff --git a/traildepot/migrations/main/U1766766317__alter_table_profile.sql b/traildepot/migrations/main/U1766766317__alter_table_profile.sql @@ -0,0 +1,27 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_profile" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'user_id' BLOB NOT NULL REFERENCES '_user'('id') UNIQUE, + 'name' TEXT NOT NULL +) STRICT; + +INSERT INTO + "__alter_table_profile" ("id", "user_id", "name") +SELECT + "id", + "_user_id", + "name" +FROM + "profile"; + +DROP TABLE "profile"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_profile" RENAME TO "profile"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766766830__update.sql b/traildepot/migrations/main/U1766766830__update.sql @@ -0,0 +1,5 @@ +CREATE VIEW profile_chats AS +SELECT cp.profile_id, c.* +FROM chat AS c +INNER JOIN chat_participation AS cp + ON cp.chat_id = c.id; diff --git a/traildepot/migrations/main/U1766771022__update.sql b/traildepot/migrations/main/U1766771022__update.sql @@ -0,0 +1,5 @@ +CREATE VIEW user_chats AS +SELECT cp.user_id, c.* +FROM chat AS c +INNER JOIN chat_participation AS cp + ON cp.chat_id = c.id; diff --git a/traildepot/migrations/main/U1766771090__alter_table_message.sql b/traildepot/migrations/main/U1766771090__alter_table_message.sql @@ -0,0 +1,37 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_message" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'content' TEXT NOT NULL, + 'sender_id' BLOB NULL REFERENCES '_user'('id'), + 'sent_at' TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP +) STRICT; + +INSERT INTO + "__alter_table_message" ( + "content", + "id", + "sent_at", + "chat_id", + "sender_id" + ) +SELECT + "content", + "id", + "sent_at", + "chat_id", + "sender_id" +FROM + "message"; + +DROP TABLE "message"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_message" RENAME TO "message"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766771122__alter_table_chat_participation.sql b/traildepot/migrations/main/U1766771122__alter_table_chat_participation.sql @@ -0,0 +1,27 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat_participation" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'profile_id' BLOB NOT NULL REFERENCES '_user'('id') +) STRICT; + +INSERT INTO + "__alter_table_chat_participation" ("chat_id", "id", "profile_id") +SELECT + "chat_id", + "id", + "profile_id" +FROM + "chat_participation"; + +DROP TABLE "chat_participation"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat_participation" RENAME TO "chat_participation"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766771132__alter_table_chat_participation.sql b/traildepot/migrations/main/U1766771132__alter_table_chat_participation.sql @@ -0,0 +1,27 @@ +PRAGMA foreign_keys = OFF; + +CREATE TABLE "__alter_table_chat_participation" ( + 'id' INTEGER PRIMARY KEY NOT NULL, + 'chat_id' INTEGER NOT NULL REFERENCES 'chat'('id'), + 'user_id' BLOB NOT NULL REFERENCES '_user'('id') +) STRICT; + +INSERT INTO + "__alter_table_chat_participation" ("user_id", "id", "chat_id") +SELECT + "profile_id", + "id", + "chat_id" +FROM + "chat_participation"; + +DROP TABLE "chat_participation"; + +PRAGMA legacy_alter_table = ON; + +ALTER TABLE + "__alter_table_chat_participation" RENAME TO "chat_participation"; + +PRAGMA legacy_alter_table = OFF; + +PRAGMA foreign_keys = ON; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766775690__drop_view_profile_chats.sql b/traildepot/migrations/main/U1766775690__drop_view_profile_chats.sql @@ -0,0 +1 @@ +DROP VIEW IF EXISTS "profile_chats"; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766775693__drop_view_user_profile.sql b/traildepot/migrations/main/U1766775693__drop_view_user_profile.sql @@ -0,0 +1 @@ +DROP VIEW IF EXISTS "user_profile"; +\ No newline at end of file diff --git a/traildepot/migrations/main/U1766779524__update.sql b/traildepot/migrations/main/U1766779524__update.sql @@ -0,0 +1,7 @@ +CREATE TRIGGER chat_participation_insert +AFTER INSERT ON chat +FOR EACH ROW +BEGIN + INSERT INTO chat_participation (chat_id, user_id) + VALUES (NEW.id, NEW.created_by); +END;