3 id UUID
PRIMARY KEY DEFAULT uuid_generate_v1(), -- v1 timebased is best for indexes
4 created
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
5 url
TEXT NOT NULL UNIQUE,
6 lease_seconds_preferred
INTERVAL,
7 lease_seconds_min
INTERVAL,
8 lease_seconds_max
INTERVAL,
9 publisher_validation_url
TEXT,
10 content_hash_algorithm
VARCHAR(16) NOT NULL DEFAULT 'sha512',
11 -- end of topic config/behavior values
13 is_active
BOOLEAN DEFAULT false, -- will be active after first successful fetch
14 is_deleted
BOOLEAN DEFAULT false, -- topic deletion pending on 'denied' notification to active subscriptions
16 last_publish
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of latest publish notification
17 content_fetch_next_attempt
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of next content update attempt
18 content_fetch_attempts_since_success
INTEGER NOT NULL DEFAULT 0,
20 content_updated
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of latest content update
23 content_type
VARCHAR(255)
25 CREATE INDEX topic_content_updated_idx
ON topic(content_updated
);
26 CREATE INDEX topic_content_fetch_next_attempt_idx
ON topic(content_fetch_next_attempt
); -- sort out which need updates
28 CREATE TABLE topic_fetch_in_progress (
29 id UUID
PRIMARY KEY NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
30 claimant UUID
NOT NULL,
31 claimed
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
32 claim_expires
TIMESTAMP WITH TIME ZONE NOT NULL
34 CREATE INDEX topic_fetch_in_progress_claim_expires_idx
ON topic_fetch_in_progress(claim_expires
);
36 CREATE VIEW topic_fetch_in_progress_active
AS
38 FROM topic_fetch_in_progress
39 WHERE claim_expires
>= now()
42 CREATE VIEW topic_fetch_needed
AS
48 content_fetch_next_attempt
<= now()
50 id NOT IN (SELECT id FROM topic_fetch_in_progress_active
)
51 ORDER BY last_publish
ASC
54 -- send notices when topic is updated, for any nodes caching content
56 CREATE OR REPLACE FUNCTION topic_changed()
63 payload
= CAST(NEW.
id AS text);
64 PERFORM
pg_notify('topic_changed', payload
);
70 CREATE TRIGGER topic_changed
73 EXECUTE PROCEDURE topic_changed()
78 CREATE TABLE subscription (
79 id UUID
PRIMARY KEY DEFAULT uuid_generate_v1(),
80 created
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
82 topic_id UUID
NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
83 callback
TEXT NOT NULL,
84 CONSTRAINT subscription_reference
UNIQUE (topic_id
, callback
),
86 verified
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
87 expires
TIMESTAMP WITH TIME ZONE NOT NULL,
90 signature_algorithm
VARCHAR(16) DEFAULT 'sha512',
91 http_remote_addr
TEXT,
94 content_delivered
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
95 delivery_attempts_since_success
INTEGER NOT NULL DEFAULT 0,
96 delivery_next_attempt
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
98 CREATE INDEX subscription_content_delivered_idx
ON subscription(content_delivered
);
99 CREATE INDEX subscription_expires_idx
ON subscription(expires
);
101 CREATE TABLE subscription_delivery_in_progress (
102 id UUID
PRIMARY KEY NOT NULL REFERENCES subscription(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
103 claimant UUID
NOT NULL,
104 claimed
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
105 claim_expires
TIMESTAMP WITH TIME ZONE NOT NULL
107 CREATE INDEX subscription_delivery_in_progress_claim_expires_idx
ON subscription_delivery_in_progress(claim_expires
);
109 CREATE VIEW subscription_delivery_in_progress_active
AS
111 FROM subscription_delivery_in_progress
112 WHERE claim_expires
>= now()
115 CREATE VIEW subscription_delivery_needed
AS
117 FROM subscription s
JOIN topic t
ON s.topic_id
= t.
id
121 s.content_delivered
< t.content_updated
123 s.delivery_next_attempt
< now()
125 s.
id NOT IN (SELECT id FROM subscription_delivery_in_progress_active
)
130 CREATE TABLE verification (
131 id UUID
PRIMARY KEY DEFAULT uuid_generate_v1(),
132 created
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
134 topic_id UUID
NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
135 callback
TEXT NOT NULL, -- hub.callback
136 -- no unique constraint, multiple verifications can be pending
139 signature_algorithm
VARCHAR(16) DEFAULT 'sha512',
140 http_remote_addr
TEXT,
143 mode TEXT NOT NULL, -- hub.mode
144 reason
TEXT, -- denials may have a reason
145 lease_seconds
INTEGER NOT NULL, -- 68 years should be long enough
146 is_publisher_validated
BOOLEAN NOT NULL DEFAULT false,
147 request_id
TEXT, -- client request which created this verification, null if server-generated (ie topic delete)
149 next_attempt
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
150 attempts
INTEGER NOT NULL DEFAULT 0
152 CREATE INDEX verification_reference_idx
ON verification(topic_id
, callback
, created
);
154 CREATE TABLE verification_in_progress (
155 id UUID
PRIMARY KEY NOT NULL REFERENCES verification(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
157 topic_id UUID
NOT NULL REFERENCES topic(id),
158 callback
TEXT NOT NULL,
159 CONSTRAINT verification_in_progress_reference
UNIQUE (topic_id
, callback
),
161 claimant UUID
NOT NULL,
162 claimed
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
163 claim_expires
TIMESTAMP WITH TIME ZONE NOT NULL
165 CREATE INDEX verification_in_progress_claim_expires_idx
ON verification_in_progress(claim_expires
);
167 CREATE VIEW verification_in_progress_active
AS
169 FROM verification_in_progress
170 WHERE claim_expires
>= now()
173 CREATE VIEW verification_needed
AS
177 (topic_id
, callback
, created
) IN (SELECT topic_id
, callback
, max(created
) AS created
FROM verification
GROUP BY topic_id
, callback
)
179 (topic_id
, callback
) NOT IN (SELECT topic_id
, callback
FROM verification_in_progress_active
)
181 next_attempt
<= now()
186 CREATE TABLE authentication (
187 created
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
188 last_authentication
TIMESTAMP WITH TIME ZONE,
189 identifier
TEXT NOT NULL PRIMARY KEY,
193 -- Update schema version
194 INSERT INTO _meta_schema_version (major
, minor
, patch
) VALUES (1, 0, 0);