Updated sql create script

1 files changed, 19 insertions, 5 deletions

diff --git a/db/0-create.psql b/db/0-create.psql
index d06fde4..01831e5 100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -78,16 +78,30 @@ INSERT INTO rooms (key, name, description, admin_only)
 INSERT INTO rooms (key, name, description, admin_only)
        VALUES ('VIP', 'The VIP Room', 'Command Post', true);
        
+
+-- Initially, feed_images are added with no message_id,
+-- and are used as a queue of pending messages to post.
+-- When the image is posted, message_id is updated to link
+-- to the resulting message.
 CREATE TABLE feed_images (
   feed_url text NOT NULL,
+  link text NOT NULL,
+  title text NOT NULL,
   image_url text NOT NULL,
-  room_id integer REFERENCES rooms,
+  room text NOT NULL,
   message_id integer REFERENCES messages,
-  added_on timestamp NOT NULL DEFAULT now(),
-  UNIQUE (room_id, image_url)
+  queued_on timestamp NOT NULL DEFAULT now(),
+  UNIQUE (room, image_url)
 );
 
-CREATE INDEX feed_images_url_idx ON feed_images (image_url);
-CREATE INDEX feed_images_url_room_idx ON feed_images (image_url, room_id);
+CREATE INDEX feed_images_url_room_idx ON feed_images (image_url, room);
+CREATE INDEX feed_images_room_message_idx ON feed_images (room, message_id);
 
+CREATE TABLE invalid_feed_images (
+  image_url text NOT NULL,
+  reason text NOT NULL,
+  added_on timestamp NOT NULL DEFAULT now(),
+  UNIQUE (image_url)
+);
 
+CREATE INDEX invalid_feed_images_idx ON invalid_feed_images (image_url);