24 lines
No EOL
869 B
SQL
24 lines
No EOL
869 B
SQL
-- 003_email_notify.sql -- email suggestions + notification log
|
|
|
|
CREATE TABLE IF NOT EXISTS email_suggestion (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
application_id uuid REFERENCES application(id) ON DELETE SET NULL,
|
|
mailbox_from text NOT NULL,
|
|
subject text NOT NULL,
|
|
snippet text NOT NULL,
|
|
classification text NOT NULL CHECK (classification IN ('interview_invite','rejection','question','noise')),
|
|
state_proposal text,
|
|
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','accepted','dismissed')),
|
|
received_at timestamptz NOT NULL,
|
|
created_at timestamptz DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_log (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel text NOT NULL,
|
|
kind text NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
delivered boolean NOT NULL,
|
|
error text,
|
|
created_at timestamptz DEFAULT now()
|
|
); |