jam-cloud/db/up/email_batch.sql

CREATE TABLE email_batch_sets (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    email_batch_id VARCHAR(64) REFERENCES email_batches(id) ON DELETE CASCADE,

    started_at TIMESTAMP,
    user_ids TEXT NOT NULL default '',
    batch_count INTEGER,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE email_batch_sets ADD CONSTRAINT email_batch_set_uniqkey UNIQUE (email_batch_id, started_at);
CREATE INDEX email_batch_set_fkidx ON email_batch_sets (email_batch_id);

CREATE TABLE email_errors (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(64) REFERENCES users(id) ON DELETE CASCADE,

    error_type VARCHAR(32),
    email_address VARCHAR(256),
    status VARCHAR(32),
    email_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason TEXT,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX email_error_user_fkidx ON email_errors(user_id);
CREATE INDEX email_error_address_idx ON email_errors(email_address);