Julia Lange
8288620f2e
Adds a migration for an initial schema. Adds sqlx-cli to the flake so that I use those migrations easily. adds an UNTESTED "direct" interface, which should make writing ingestors easier, and be very easy for spoor lexicons. It will probably also be easy for other lexicons if they can fit into the Activity -> Session -> Content interface. Adds errors because I needed errors. Since this is a library, and as you can see there are no tests, that means that everything is *untested* which is fine because I want a MVP not a stable app. For now.
45 lines
1 KiB
SQL
45 lines
1 KiB
SQL
-- Add migration script here
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE TABLE actor (
|
|
did VARCHAR PRIMARY KEY,
|
|
handle VARCHAR UNIQUE,
|
|
indexed_at VARCHAR NOT NULL
|
|
);
|
|
CREATE INDEX actor_handle_trgm_idx ON actor USING gist (handle gist_trgm_ops);
|
|
|
|
CREATE TABLE session (
|
|
uri VARCHAR PRIMARY KEY,
|
|
cid VARCHAR NOT NULL,
|
|
owner VARCHAR NOT NULL,
|
|
|
|
content VARCHAR NOT NULL,
|
|
contentcid VARCHAR NOT NULL,
|
|
label VARCHAR,
|
|
-- Participants in participant
|
|
|
|
created_at VARCHAR,
|
|
indexed_at VARCHAR NOT NULL,
|
|
sort_at VARCHAR GENERATED ALWAYS AS (LEAST(created_at,indexed_at)) STORED NOT NULL
|
|
);
|
|
|
|
CREATE TABLE activity (
|
|
uri VARCHAR PRIMARY KEY,
|
|
cid VARCHAR NOT NULL,
|
|
|
|
session VARCHAR,
|
|
sessioncid VARCHAR,
|
|
-- Progress in progress
|
|
|
|
performed_at VARCHAR,
|
|
created_at VARCHAR,
|
|
indexed_at VARCHAR NOT NULL,
|
|
sort_at VARCHAR GENERATED ALWAYS AS (LEAST(created_at,indexed_at)) STORED NOT NULL
|
|
);
|
|
|
|
CREATE TABLE participant (
|
|
participantdid VARCHAR NOT NULL,
|
|
sessionuri VARCHAR NOT NULL,
|
|
role VARCHAR NOT NULL
|
|
);
|