+create table notice_inbox (\r
+\r
+ user_id integer not null /* comment 'user receiving the message' */ references "user" (id),\r
+ notice_id integer not null /* comment 'notice received' */ references notice (id),\r
+ created timestamp not null /* comment 'date the notice was created' */,\r
+ source integer default 1 /* comment 'reason it is in the inbox; 1=subscription' */,\r
+\r
+ primary key (user_id, notice_id)\r
+);\r
+create index notice_inbox_notice_id_idx on notice_inbox using btree(notice_id);\r
+\r
+create table profile_tag (\r
+ tagger integer not null /* comment 'user making the tag' */ references "user" (id),\r
+ tagged integer not null /* comment 'profile tagged' */ references profile (id),\r
+ tag varchar(64) not null /* comment 'hash tag associated with this notice' */,\r
+ modified timestamp /* comment 'date the tag was added' */,\r
+\r
+ primary key (tagger, tagged, tag)\r
+);\r
+create index profile_tag_modified_idx on profile_tag using btree(modified);\r
+create index profile_tag_tagger_tag_idx on profile_tag using btree(tagger,tag);\r
+\r
+create table profile_block (\r
+\r
+ blocker integer not null /* comment 'user making the block' */ references "user" (id),\r
+ blocked integer not null /* comment 'profile that is blocked' */ references profile (id),\r
+ modified timestamp /* comment 'date of blocking' */,\r
+\r
+ primary key (blocker, blocked)\r
+\r
+);\r
+\r
+create table user_group (\r
+\r
+ id serial primary key /* comment 'unique identifier' */,\r
+\r
+ nickname varchar(64) unique /* comment 'nickname for addressing' */,\r
+ fullname varchar(255) /* comment 'display name' */,\r
+ homepage varchar(255) /* comment 'URL, cached so we dont regenerate' */,\r
+ description varchar(140) /* comment 'descriptive biography' */,\r
+ location varchar(255) /* comment 'related physical location, if any' */,\r
+\r
+ original_logo varchar(255) /* comment 'original size logo' */,\r
+ homepage_logo varchar(255) /* comment 'homepage (profile) size logo' */,\r
+ stream_logo varchar(255) /* comment 'stream-sized logo' */,\r
+ mini_logo varchar(255) /* comment 'mini logo' */,\r
+\r
+ created timestamp not null /* comment 'date this record was created' */,\r
+ modified timestamp /* comment 'date this record was modified' */\r
+\r
+);\r
+create index user_group_nickname_idx on user_group using btree(nickname);\r
+\r
+create table group_member (\r
+\r
+ group_id integer not null /* comment 'foreign key to user_group' */ references user_group (id),\r
+ profile_id integer not null /* comment 'foreign key to profile table' */ references profile (id),\r
+ is_admin integer default 0 /* comment 'is this user an admin?' */,\r
+\r
+ created timestamp not null /* comment 'date this record was created' */,\r
+ modified timestamp /* comment 'date this record was modified' */,\r
+\r
+ primary key (group_id, profile_id)\r
+);\r
+\r
+create table related_group (\r
+\r
+ group_id integer not null /* comment 'foreign key to user_group' */ references user_group (id) ,\r
+ related_group_id integer not null /* comment 'foreign key to user_group' */ references user_group (id),\r
+\r
+ created timestamp not null /* comment 'date this record was created' */,\r
+\r
+ primary key (group_id, related_group_id)\r
+\r
+);\r
+\r
+create table group_inbox (\r
+ group_id integer not null /* comment 'group receiving the message' references user_group (id) */,\r
+ notice_id integer not null /* comment 'notice received' references notice (id) */,\r
+ created timestamp not null /* comment 'date the notice was created' */,\r
+\r
+ primary key (group_id, notice_id)\r
+);\r
+create index group_inbox_created_idx on group_inbox using btree(created);\r
+\r