database work

1 files changed, 12 insertions, 8 deletions

diff --git a/migrations/20241113160730_critch.sql b/migrations/20241113160730_critch.sql
index 7e4b19e..131daf3 100644
--- a/migrations/20241113160730_critch.sql
+++ b/migrations/20241113160730_critch.sql
@@ -2,7 +2,8 @@ create extension if not exists "uuid-ossp";
 
 create table artists (
     id integer primary key generated always as identity,
-    artist_name varchar(128) not null unique,
+    handle varchar(128) not null unique,
+    name varchar(128),
     bio text,
     site varchar(256)
 );
@@ -12,6 +13,7 @@ create table artworks (
     title varchar(256),
     description text,
     url_source varchar(256),
+    created_at timestamp not null default current_timestamp,
     artist_id integer not null,
     comment_number integer not null default 0,
     foreign key (artist_id) references artists(id)
@@ -20,24 +22,26 @@ create table artworks (
 create table comments (
     id integer unique not null,
     text text not null,
-    thread_id integer not null,
-    primary key (id, thread_id),
-    foreign key (thread_id) references artworks(id)
+    artwork_id integer not null,
+    created_at timestamp not null default current_timestamp,
+    primary key (id, artwork_id),
+    foreign key (artwork_id) references artworks(id)
 );
 
 create table comment_relations (
-    thread_id integer,
-    foreign key (thread_id) references artworks(id),
+    artwork_id integer,
+    foreign key (artwork_id) references artworks(id),
     in_reply_to_id integer,
     foreign key (in_reply_to_id) references comments(id),
     comment_id integer,
     foreign key (comment_id) references comments(id),
-    primary key (thread_id, in_reply_to_id, comment_id)
+    primary key (artwork_id, in_reply_to_id, comment_id)
 );
 
-create table files (
+create table artwork_files (
     id uuid primary key default gen_random_uuid(),
     alt_text text,
+    extension varchar(16),
     artwork_id integer,
     foreign key (artwork_id) references artworks(id)
 );