diff options
Diffstat (limited to 'crates/turtle/db/server-sqlite-migrations')
7 files changed, 68 insertions, 0 deletions
diff --git a/crates/turtle/db/server-sqlite-migrations/20231203124112_create-store.sql b/crates/turtle/db/server-sqlite-migrations/20231203124112_create-store.sql new file mode 100644 index 00000000..ca19ed62 --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20231203124112_create-store.sql @@ -0,0 +1,17 @@ +create table store ( + id text primary key, -- remember to use uuidv7 for happy indices <3 + client_id text not null, -- I am too uncomfortable with the idea of a client-generated primary key, even though it's fine mathematically + host text not null, -- a unique identifier for the host + idx bigint not null, -- the index of the record in this store, identified by (host, tag) + timestamp bigint not null, -- not a timestamp type, as those do not have nanosecond precision + version text not null, + tag text not null, -- what is this? history, kv, whatever. Remember clients get a log per tag per host + data text not null, -- store the actual history data, encrypted. I don't wanna know! + cek text not null, + + user_id bigint not null, -- allow multiple users + created_at timestamp not null default current_timestamp +); + +create unique index record_uniq ON store(user_id, host, tag, idx); + diff --git a/crates/turtle/db/server-sqlite-migrations/20240108124830_create-history.sql b/crates/turtle/db/server-sqlite-migrations/20240108124830_create-history.sql new file mode 100644 index 00000000..7bd653ba --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20240108124830_create-history.sql @@ -0,0 +1,15 @@ +create table history ( + id integer primary key autoincrement, + client_id text not null unique, -- the client-generated ID + user_id bigserial not null, -- allow multiple users + hostname text not null, -- a unique identifier from the client (can be hashed, random, whatever) + timestamp timestamp not null, -- one of the few non-encrypted metadatas + + data text not null, -- store the actual history data, encrypted. I don't wanna know! + + created_at timestamp not null default current_timestamp, + deleted_at timestamp +); + +create unique index history_deleted_index on history(client_id, user_id, deleted_at); + diff --git a/crates/turtle/db/server-sqlite-migrations/20240108124831_create-sessions.sql b/crates/turtle/db/server-sqlite-migrations/20240108124831_create-sessions.sql new file mode 100644 index 00000000..3120c35d --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20240108124831_create-sessions.sql @@ -0,0 +1,6 @@ +create table sessions ( + id integer primary key autoincrement, + user_id integer, + token text unique not null +); + diff --git a/crates/turtle/db/server-sqlite-migrations/20240621110730_create-users.sql b/crates/turtle/db/server-sqlite-migrations/20240621110730_create-users.sql new file mode 100644 index 00000000..852c159d --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20240621110730_create-users.sql @@ -0,0 +1,12 @@ +create table users ( + id integer primary key autoincrement, -- also store our own ID + username text not null unique, -- being able to contact users is useful + email text not null unique, -- being able to contact users is useful + password text not null unique, + created_at timestamp not null default (datetime('now','localtime')), + verified_at timestamp with time zone default null +); + +-- the prior index is case sensitive :( +CREATE UNIQUE INDEX email_unique_idx on users (LOWER(email)); +CREATE UNIQUE INDEX username_unique_idx on users (LOWER(username)); diff --git a/crates/turtle/db/server-sqlite-migrations/20240621110731_create-user-verification-token.sql b/crates/turtle/db/server-sqlite-migrations/20240621110731_create-user-verification-token.sql new file mode 100644 index 00000000..36eb14de --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20240621110731_create-user-verification-token.sql @@ -0,0 +1,6 @@ +create table user_verification_token( + id integer primary key autoincrement, + user_id bigint unique references users(id), + token text, + valid_until timestamp with time zone +); diff --git a/crates/turtle/db/server-sqlite-migrations/20240702094825_create-store-idx-cache.sql b/crates/turtle/db/server-sqlite-migrations/20240702094825_create-store-idx-cache.sql new file mode 100644 index 00000000..cd54cb18 --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20240702094825_create-store-idx-cache.sql @@ -0,0 +1,10 @@ +create table store_idx_cache( + id integer primary key autoincrement, + user_id bigint, + + host uuid, + tag text, + idx bigint +); + +create unique index store_idx_cache_uniq on store_idx_cache(user_id, host, tag); diff --git a/crates/turtle/db/server-sqlite-migrations/20260127000000_remove-email-verification.sql b/crates/turtle/db/server-sqlite-migrations/20260127000000_remove-email-verification.sql new file mode 100644 index 00000000..0bde89d7 --- /dev/null +++ b/crates/turtle/db/server-sqlite-migrations/20260127000000_remove-email-verification.sql @@ -0,0 +1,2 @@ +drop table if exists user_verification_token; +alter table users drop column verified_at; |