--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/pubsub.sql	Tue Nov 16 10:37:01 2004 +0000
@@ -0,0 +1,40 @@
+create table entities (
+    id serial primary key,
+    jid text not null unique
+);
+
+create table nodes (
+    id serial primary key,
+    node text not null unique,
+    persistent boolean not null default true,
+    deliver_payload boolean not null default true
+);
+
+create table affiliations (
+    id serial primary key,
+    entity_id integer not null references entities on delete cascade,
+    node_id integer not null references nodes on delete cascade,
+    affiliation text not null
+        check (affiliation in ('outcast', 'publisher', 'owner')),
+    unique (entity_id, node_id)
+);
+
+create table subscriptions (
+    id serial primary key,
+    entity_id integer not null references entities on delete cascade,
+    resource text,
+    node_id integer not null references nodes on delete cascade,
+    subscription text not null default 'subscribed' check
+        (subscription in ('subscribed', 'pending', 'unconfigured')),
+    unique (entity_id, resource, node_id)
+);
+
+create table items (
+    id serial primary key,
+    node_id integer not null references nodes on delete cascade,
+    item text not null,
+    publisher text not null,
+    data text,
+    date timestamp with time zone not null default now(),
+    unique (node_id, item)
+);