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.
22 lines
729 B
SQL
22 lines
729 B
SQL
-- +goose up
|
|
CREATE TABLE IF NOT EXISTS analytics_posts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
post_id TEXT NOT NULL,
|
|
post_text TEXT NOT NULL,
|
|
post_timestamp TIMESTAMPTZ NOT NULL,
|
|
likes BIGINT NOT NULL DEFAULT 0,
|
|
reposts BIGINT NOT NULL DEFAULT 0,
|
|
replies BIGINT NOT NULL DEFAULT 0,
|
|
quotes BIGINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, platform, post_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_analytics_posts_user_platform_time
|
|
ON analytics_posts (user_id, platform, post_timestamp);
|
|
|
|
-- +goose down
|
|
DROP INDEX IF EXISTS idx_analytics_posts_user_platform_time;
|
|
DROP TABLE IF EXISTS analytics_posts;
|