Nacorid
ab1eb634f9
Add tables backing the analytics cache, the permanent per-platform post store, and the coverage watermark used to avoid re-fetching paid Twitter posts once a time range has already been retrieved.
23 lines
No EOL
755 B
SQL
23 lines
No EOL
755 B
SQL
-- +goose up
|
|
CREATE TABLE IF NOT EXISTS analytics_cache (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
tool_name TEXT NOT NULL,
|
|
time_range_hash TEXT NOT NULL,
|
|
result JSONB NOT NULL,
|
|
computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
UNIQUE (user_id, platform, tool_name, time_range_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_analytics_cache_lookup
|
|
ON analytics_cache (user_id, platform, tool_name, time_range_hash);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_analytics_cache_expires
|
|
ON analytics_cache (expires_at);
|
|
|
|
-- +goose down
|
|
DROP INDEX IF EXISTS idx_analytics_cache_expires;
|
|
DROP INDEX IF EXISTS idx_analytics_cache_lookup;
|
|
DROP TABLE IF EXISTS analytics_cache; |