27 lines
1.2 KiB
SQL
27 lines
1.2 KiB
SQL
-- M4a: outbound ListenBrainz scrobble worker.
|
|
-- Per-user LB config (plaintext token; users rotate via /settings if leaked).
|
|
-- scrobble_queue is a work list, not a log: successfully-sent rows are
|
|
-- DELETEd by the worker (the canonical record stays in play_events.scrobbled_at
|
|
-- via M2's column). Only `pending` and `failed` states exist.
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN listenbrainz_token TEXT NULL,
|
|
ADD COLUMN listenbrainz_enabled BOOLEAN NOT NULL DEFAULT FALSE;
|
|
|
|
CREATE TABLE scrobble_queue (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
play_event_id uuid NOT NULL REFERENCES play_events(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL CHECK (status IN ('pending', 'failed')),
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
next_attempt_at timestamptz NOT NULL DEFAULT now(),
|
|
last_error TEXT NULL,
|
|
enqueued_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (play_event_id)
|
|
);
|
|
|
|
-- Hot path for the worker's per-tick query.
|
|
CREATE INDEX scrobble_queue_pending_idx
|
|
ON scrobble_queue (next_attempt_at)
|
|
WHERE status = 'pending';
|