68 lines
1.8 KiB
SQL
68 lines
1.8 KiB
SQL
-- 2023-04-04: Create initial tables
|
|
|
|
-- +migrate Up
|
|
|
|
-- User accounts
|
|
-- Many fields in this table are only relevant for local users, but all blogs are associated with an account
|
|
-- to make the database schema simpler.
|
|
create table accounts (
|
|
id text primary key,
|
|
username text not null,
|
|
domain text, -- if null, is a local account
|
|
email text,
|
|
password bytea,
|
|
|
|
unique (username, domain)
|
|
);
|
|
|
|
-- Blogs
|
|
-- These are the AP actors.
|
|
create table blogs (
|
|
id text primary key,
|
|
name text not null,
|
|
domain text, -- if null, is a local blog
|
|
bio text not null,
|
|
|
|
account_id text not null references accounts (id) on delete cascade,
|
|
|
|
unique (name, domain)
|
|
);
|
|
|
|
create type post_visibility as enum ('public', 'unlisted', 'followers', 'direct');
|
|
|
|
create table posts (
|
|
id text primary key,
|
|
blog_id text not null references blogs (id) on delete cascade,
|
|
content text,
|
|
source text,
|
|
visibility post_visibility not null
|
|
);
|
|
|
|
create table applications (
|
|
id text primary key,
|
|
name text not null
|
|
);
|
|
|
|
create table tokens (
|
|
id text primary key,
|
|
app_id text not null references applications (id) on delete cascade,
|
|
user_id text not null references accounts (id) on delete cascade,
|
|
scopes text[] not null default array[]::text[],
|
|
expires timestamptz not null
|
|
);
|
|
|
|
create table config (
|
|
id int primary key not null default 1,
|
|
name text not null, -- instance name
|
|
admin_id text references blogs (id) on delete set null, -- admin contact
|
|
internal_application text references applications (id) on delete set null,
|
|
constraint singleton check (id = 1) -- only one config table entry
|
|
);
|
|
|
|
-- +migrate Down
|
|
drop table config;
|
|
drop table tokens;
|
|
drop table applications;
|
|
drop table accounts;
|
|
drop table blogs;
|
|
drop table posts;
|