schema

CREATE TABLE Applicants (
    id UUID PRIMARY KEY,
    user_id INTEGER NOT NULL,
    client_id UUID NOT NULL,
    first_name VARCHAR(255) NOT NULL CHECK (first_name <> ''),
    last_name VARCHAR(255) NOT NULL CHECK (last_name <> ''),
    email_address VARCHAR(255),

    UNIQUE (user_id),
    FOREIGN KEY (client_id) REFERENCES Clients(id) ON DELETE CASCADE
);

CREATE TABLE Applications (
    id UUID PRIMARY KEY,
    applicant_id UUID NOT NULL,

    probability REAL,
    minimum_probability REAL NOT NULL,
    facility_lookup_name VARCHAR(255) NOT NULL,
    job_category_lookup VARCHAR(255),
    time_finish DATE,
    create_date DATE,
    is_job_category_assigned BOOL NOT NULL,
    archive_days INTEGER NOT NULL,

    FOREIGN KEY (applicant_id) REFERENCES Applicants(id)
);

CREATE TABLE ApplicationHireEvent (
    application_id UUID NOT NULL,
    hire_id UUID NOT NULL,

    UNIQUE (application_id),
    UNIQUE (hire_id),

    FOREIGN KEY (application_id) REFERENCES Applications(id),
    FOREIGN KEY (hire_id) REFERENCES Events(id)
);