22 lines
812 B
SQL
22 lines
812 B
SQL
CREATE TABLE IF NOT EXISTS reminders (
|
|
id UUID PRIMARY KEY,
|
|
user_uuid UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
message TEXT NOT NULL,
|
|
timezone VARCHAR(64) NOT NULL,
|
|
recurrence JSONB,
|
|
next_run_at TIMESTAMPTZ NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ,
|
|
cancelled_at TIMESTAMPTZ,
|
|
CONSTRAINT reminders_status_check
|
|
CHECK (status IN ('active', 'completed', 'cancelled')),
|
|
CONSTRAINT reminders_recurrence_check
|
|
CHECK (recurrence IS NULL OR jsonb_typeof(recurrence) = 'object')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS reminders_user_active_idx
|
|
ON reminders (user_uuid, next_run_at)
|
|
WHERE status = 'active';
|