18 lines
724 B
SQL
18 lines
724 B
SQL
-- Delayed order emails table
|
|
|
|
CREATE TYPE delayed_orders_email_type AS ENUM ('automatic', 'manual');
|
|
CREATE TYPE delayed_orders_email_status as ENUM ('created', 'sent', 'error', 'blocked');
|
|
|
|
CREATE TABLE delayed_orders_email (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id int REFERENCES orders (id) NOT NULL,
|
|
type delayed_orders_email_type NOT NULL,
|
|
status delayed_orders_email_status NOT NULL,
|
|
date timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
-- This creates a partial unique constraint.
|
|
-- It enforces max one automatic email; allows multiple manual emails; enables blocking automatic email.
|
|
CREATE UNIQUE INDEX unique_automatic_delayed_orders_emails ON delayed_orders_email (order_id) WHERE (type = 'automatic');
|
|
|