2 files changed, 77 insertions, 0 deletions
diff --git a/migrations/0001_init.sql b/migrations/0001_init.sql
new file mode 100644
index 0000000..c9915eb
--- /dev/null
+++ b/migrations/0001_init.sql
@@ -0,0 +1,62 @@
+CREATE SCHEMA IF NOT EXISTS kittybox;
+
+CREATE TABLE kittybox.users (
+    user_domain TEXT NOT NULL PRIMARY KEY,
+    site_name   JSONB NOT NULL DEFAULT '"Kittybox"'::jsonb,
+    webring     JSONB NOT NULL DEFAULT 'false'::jsonb
+);
+
+CREATE TABLE kittybox.mf2_json (
+    uid TEXT NOT NULL PRIMARY KEY,
+    mf2 JSONB NOT NULL,
+    owner TEXT NOT NULL -- REFERENCES kittybox.users(user_domain)
+);
+
+CREATE INDEX mf2props ON kittybox.mf2_json USING GIN (mf2);
+CREATE INDEX published_date ON kittybox.mf2_json ((mf2 #>> '{properties,published,0}'));
+
+CREATE TABLE kittybox.children (
+    parent TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
+    child  TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
+    UNIQUE(parent, child)
+);
+
+CREATE INDEX fulltext ON kittybox.mf2_json USING GIN (
+    to_tsvector('english', mf2['properties']['content'])
+);
+
+CREATE FUNCTION kittybox.set_setting(user_domain text, setting text, val anyelement) RETURNS void AS $$
+BEGIN
+EXECUTE format('INSERT INTO kittybox.users (user_domain, %I) VALUES ($1, $2) ON CONFLICT (user_domain) DO UPDATE SET %I = $2', setting, setting)
+    USING user_domain, val;
+    RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION kittybox.get_setting(user_domain text, setting text) RETURNS jsonb AS $$
+DECLARE
+  val jsonb;
+BEGIN
+EXECUTE format('SELECT %I FROM kittybox.users WHERE user_domain = $1', setting) USING user_domain INTO val;
+
+RETURN val;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION kittybox.hydrate_author(mf2 jsonb) RETURNS jsonb AS $$
+DECLARE
+  author jsonb;
+  author_uid text;
+BEGIN
+
+author_uid := mf2 #>> '{properties,author,0}';
+IF NOT (author_uid IS NULL) THEN
+   SELECT mf2_json.mf2 INTO author FROM kittybox.mf2_json WHERE uid = author_uid;
+END IF;
+IF NOT FOUND THEN
+    RETURN mf2;
+ELSE
+    RETURN jsonb_set(mf2, '{properties,author,0}', author);
+END IF;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/migrations/webmention/0001_init.sql b/migrations/webmention/0001_init.sql
new file mode 100644
index 0000000..9e7a192
--- /dev/null
+++ b/migrations/webmention/0001_init.sql
@@ -0,0 +1,15 @@
+CREATE SCHEMA IF NOT EXISTS kittybox_webmention;
+
+CREATE TABLE kittybox_webmention.incoming_webmention_queue (
+       id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+       source TEXT NOT NULL,
+       target TEXT NOT NULL,
+       recv_timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
+       attempts INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE INDEX webmention_jobs_by_attempts ON kittybox_webmention.incoming_webmention_queue (attempts);
+
+CREATE RULE notify_incoming_webmention AS
+ON INSERT TO kittybox_webmention.incoming_webmention_queue
+DO ALSO NOTIFY incoming_webmention;