max / makenotwork

499 B · 13 lines History Blame Raw

1	-- Daily aggregated page view counts per target.
2	-- One row per (target_type, target_id, date). The tracking handler UPSERTs
3	-- with ON CONFLICT ... DO UPDATE SET view_count = view_count + 1.
4	CREATE TABLE page_view_daily (
5	target_type TEXT NOT NULL,
6	target_id UUID NOT NULL,
7	view_date DATE NOT NULL DEFAULT CURRENT_DATE,
8	view_count BIGINT NOT NULL DEFAULT 1,
9	PRIMARY KEY (target_type, target_id, view_date)
10	);
11
12	CREATE INDEX idx_pvd_date ON page_view_daily (view_date DESC);
13