mirror of https://gitlab.com/ngerakines/tavern.git
260 lines
9.2 KiB
SQL
260 lines
9.2 KiB
SQL
create table if not exists public.users
|
|
(
|
|
id uuid not null
|
|
constraint users_pkey
|
|
primary key,
|
|
email varchar(100)
|
|
constraint users_email_key
|
|
unique,
|
|
password bytea,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
last_auth_at timestamp with time zone,
|
|
location varchar default 'UTC'::character varying not null,
|
|
mute_email boolean default false not null,
|
|
locale varchar default 'en'::character varying not null,
|
|
public_key text not null,
|
|
private_key text not null,
|
|
name varchar not null,
|
|
display_name varchar not null,
|
|
about text not null,
|
|
accept_followers boolean default true not null,
|
|
actor_id uuid not null,
|
|
reply_collection_updates boolean default false not null
|
|
);
|
|
|
|
create unique index if not exists users_username_uindex
|
|
on public.users (name);
|
|
|
|
create table if not exists public.actors
|
|
(
|
|
id uuid not null
|
|
constraint actors_pk
|
|
primary key,
|
|
actor_id varchar not null
|
|
constraint actors_actor_id
|
|
unique,
|
|
payload jsonb not null,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone default now() not null
|
|
);
|
|
|
|
create table if not exists public.peers
|
|
(
|
|
id uuid not null
|
|
constraint peers_pk
|
|
primary key,
|
|
inbox varchar(100) not null,
|
|
created_at timestamp with time zone not null
|
|
);
|
|
|
|
create unique index if not exists peers_inbox_uindex
|
|
on public.peers (inbox);
|
|
|
|
create table if not exists public.images
|
|
(
|
|
id uuid not null
|
|
constraint asset_image_pk
|
|
primary key,
|
|
location varchar(200) not null,
|
|
checksum varchar(200) not null,
|
|
content_type integer default 0 not null,
|
|
size integer default 0 not null,
|
|
height integer default 0 not null,
|
|
width integer default 0 not null,
|
|
blur varchar(100) not null,
|
|
created_at timestamp with time zone
|
|
);
|
|
|
|
create unique index if not exists asset_image_checksum_uindex
|
|
on public.images (checksum);
|
|
|
|
create unique index if not exists asset_image_location_uindex
|
|
on public.images (location);
|
|
|
|
create table if not exists public.image_aliases
|
|
(
|
|
id uuid not null
|
|
constraint image_aliases_pk
|
|
primary key,
|
|
image_id uuid not null,
|
|
alias varchar(200) not null,
|
|
created_at timestamp with time zone not null
|
|
);
|
|
|
|
create unique index if not exists image_aliases_pairs
|
|
on public.image_aliases (image_id, alias);
|
|
|
|
create table if not exists public.schema_migrations
|
|
(
|
|
version bigint not null
|
|
constraint schema_migrations_pkey
|
|
primary key,
|
|
dirty boolean not null
|
|
);
|
|
|
|
create table if not exists public.network_graph
|
|
(
|
|
id uuid not null
|
|
constraint network_graph_pk
|
|
primary key,
|
|
user_id uuid not null,
|
|
actor_id uuid not null,
|
|
activity jsonb not null,
|
|
relationship_type integer default 0 not null,
|
|
relationship_status integer default 0 not null,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
constraint network_graph_user_actor_rel
|
|
unique (user_id, actor_id, relationship_type)
|
|
);
|
|
|
|
create table if not exists public.actor_aliases
|
|
(
|
|
id uuid not null
|
|
constraint actor_subjects_pk
|
|
primary key,
|
|
actor_id uuid not null,
|
|
alias varchar not null,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
alias_type integer default 0 not null
|
|
);
|
|
|
|
create unique index if not exists actor_aliases_alias_uindex
|
|
on public.actor_aliases (alias);
|
|
|
|
create table if not exists public.actor_keys
|
|
(
|
|
id uuid not null
|
|
constraint actor_keys_pk
|
|
primary key,
|
|
actor_id uuid not null,
|
|
key_id varchar not null,
|
|
pem text not null,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
constraint actor_keys_lookup
|
|
unique (actor_id, key_id)
|
|
);
|
|
|
|
create table if not exists public.objects
|
|
(
|
|
id uuid not null
|
|
constraint objects_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
payload jsonb not null,
|
|
object_id varchar not null
|
|
constraint objects_object_uindex
|
|
unique
|
|
);
|
|
|
|
create table if not exists public.object_events
|
|
(
|
|
id uuid not null
|
|
constraint object_events_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
activity_id varchar not null
|
|
constraint object_events_activity_uindex
|
|
unique,
|
|
object_id uuid not null,
|
|
payload jsonb not null
|
|
);
|
|
|
|
create table if not exists public.user_object_events
|
|
(
|
|
id uuid not null
|
|
constraint user_object_events_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
user_id uuid not null,
|
|
activity_id uuid not null,
|
|
object_id uuid not null,
|
|
public boolean default false not null,
|
|
constraint user_object_events_user_activity_uindex
|
|
unique (user_id, activity_id, object_id)
|
|
);
|
|
|
|
create table if not exists public.object_tags
|
|
(
|
|
id uuid not null
|
|
constraint object_tags_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
object_id uuid not null,
|
|
tag varchar not null,
|
|
constraint object_tags_tagged_uindex
|
|
unique (object_id, tag)
|
|
);
|
|
|
|
create table if not exists public.user_feed
|
|
(
|
|
id uuid not null
|
|
constraint user_feed_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
activity_id uuid not null,
|
|
object_id uuid not null,
|
|
user_id uuid not null,
|
|
constraint user_feed_activity_uindex
|
|
unique (user_id, activity_id, object_id)
|
|
);
|
|
|
|
create table if not exists public.object_replies
|
|
(
|
|
id uuid not null
|
|
constraint object_replies_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
object_id uuid not null,
|
|
parent_object_id uuid not null,
|
|
constraint object_replies_reply_uindex
|
|
unique (object_id, parent_object_id)
|
|
);
|
|
|
|
create table if not exists public.threads
|
|
(
|
|
id uuid not null
|
|
constraint threads_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
thread_hash varchar not null
|
|
constraint threads_uindex
|
|
unique
|
|
);
|
|
|
|
create table if not exists public.object_threads
|
|
(
|
|
id uuid not null
|
|
constraint object_threads_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
object_id uuid not null,
|
|
thread_id uuid not null,
|
|
constraint object_threads_uindex
|
|
unique (object_id, thread_id)
|
|
);
|
|
|
|
create table if not exists public.object_boosts
|
|
(
|
|
id uuid not null
|
|
constraint object_boosts_pk
|
|
primary key,
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
actor_id uuid not null,
|
|
activity_id uuid not null,
|
|
object_id uuid not null
|
|
);
|
|
|