initial commit

1 files changed, 109 insertions, 0 deletions

diff --git a/migrations/20231003193749_pinussy.sql b/migrations/20231003193749_pinussy.sql
new file mode 100644
index 0000000..24f3aa1
--- /dev/null
+++ b/migrations/20231003193749_pinussy.sql
@@ -0,0 +1,109 @@
+create type privacy as enum ('private', 'unlisted', 'public');
+create type file_type as enum ('image', 'video', 'audio', 'text', 'document', 'site', 'other');
+
+create table users (
+    id integer primary key generated always as identity,
+    username varchar(32) not null,
+    unique(id, username),
+    password varchar(128) not null,
+    email varchar(128),
+    bio text,
+    site varchar(128),
+    privacy privacy not null default 'public',
+    admin boolean not null default true
+);
+
+create table sessions (
+    id varchar(128) primary key,
+    expires timestamp with time zone,
+    user_id integer not null,
+    foreign key (user_id) references users(id)
+);
+
+create table boards (
+    id integer primary key generated always as identity,
+    idname varchar(256),
+    unique(id, idname),
+    name varchar(256),
+    description text,
+    privacy privacy
+);
+
+create table board_ownership (
+    board_id integer not null,
+    user_id integer not null,
+    foreign key (board_id) references boards(id),
+    foreign key (user_id) references users(id),
+    primary key (board_id, user_id)
+);
+
+create table pins (
+    id integer primary key generated always as identity,
+    idname varchar(256),
+    unique(id, idname),
+    subject varchar(256),
+    notes text,
+    privacy privacy
+);
+
+create table pin_ownership (
+    pin_id integer not null,
+    user_id integer not null,
+    foreign key (pin_id) references pins(id),
+    foreign key (user_id) references users(id),
+    primary key (pin_id, user_id)
+);
+
+create table pins_boards (
+    pin_id integer not null,
+    board_id integer not null,
+    foreign key (pin_id) references pins(id),
+    foreign key (board_id) references boards(id),
+    primary key (pin_id, board_id)
+);
+
+create table sources (
+    id integer primary key generated always as identity,
+    idname varchar(256),
+    unique(id, idname),
+    title varchar(256),
+    author varchar(256),
+    url varchar(256),
+    iban varchar(13),
+    page varchar(256),
+    location varchar(256)
+);
+
+create table pin_sources (
+    source_id integer not null,
+    pin_id integer not null,
+    foreign key (source_id) references sources(id),
+    foreign key (pin_id) references pins(id),
+    primary key (source_id, pin_id)
+);
+
+create table files (
+    id integer primary key generated always as identity,
+    idname varchar(256),
+    unique(id, idname),
+    thumbnail varchar(256),
+    path varchar(256) not null,
+    title varchar(256),
+    type file_type,
+    alt_text text,
+    pin_id integer not null,
+    foreign key (pin_id) references pins(id)
+);
+
+create table tags (
+    tag varchar(128) not null primary key
+);
+
+create table tags_pins (
+    tag varchar(128) not null,
+    pin_id integer not null,
+    foreign key (tag) references tags(tag),
+    foreign key (pin_id) references pins(id),
+    primary key (tag, pin_id)
+);
+