weichen
4e0e6a1418
Add notification digests with ON CONFLICT DO UPDATE, migrations 00068-00070, supplementimpact module (analyze → confirm → claim-aware split), and wire coordinator/mutate hooks plus proposal_required digests.
26 lines
1.0 KiB
SQL
26 lines
1.0 KiB
SQL
-- +goose Up
|
|
CREATE TABLE IF NOT EXISTS notification_digests (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
|
digest_kind TEXT NOT NULL,
|
|
doc_id TEXT NOT NULL DEFAULT '',
|
|
window_date DATE NOT NULL,
|
|
occurrence_count INT NOT NULL DEFAULT 1,
|
|
last_actor_id BIGINT,
|
|
last_actor_name TEXT NOT NULL DEFAULT '',
|
|
last_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
item_summaries JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
truncated_count INT NOT NULL DEFAULT 0,
|
|
notification_id BIGINT REFERENCES notifications(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_notification_digest_window UNIQUE (user_id, digest_kind, doc_id, window_date)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notification_digests_user_kind
|
|
ON notification_digests (user_id, digest_kind, window_date DESC);
|
|
|
|
-- +goose Down
|
|
-- record retention: do not drop table
|
|
SELECT 1;
|