create_db.sql

SOURCE SOURCE MATCHED
CREATE IS STREET TEAM ENUM
Vehicles table

CREATE TYPE FEEDBACK_TYPE as ENUM ('Positive','Neutral','Negative','Suggestion','From Partner');
CREATE TYPE STREET_TEAM_STATUS as ENUM ('Prospect',' Pending', 'Skip', 'Confirmed');
CREATE TABLE regions(
    id SERIAL PRIMARY KEY,
    region_name VARCHAR(255)
);
    CREATE TABLE shift_types(
    id SERIAL PRIMARY KEY,
    shift_type VARCHAR(255) NOT NULL
);
    CREATE TABLE sources(
    id SERIAL PRIMARY KEY,
    source_name VARCHAR(255) NOT NULL
);
    CREATE TABLE attendance_statuses(
    id SERIAL PRIMARY KEY,
    attendance_status VARCHAR(255) NOT NULL
);
    CREATE TABLE confirmation_statuses(
    id SERIAL PRIMARY KEY,
    confirmation_status VARCHAR(255) NOT NULL
);
    CREATE TABLE neighborhoods(
    id SERIAL PRIMARY KEY,
    neighborhood_name VARCHAR(255) NOT NULL
);
    CREATE TABLE volunteers(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_eligible  BOOLEAN NOT NULL,
    region_id INT NOT NULL,
    phone_number BIGINT NOT NULL,
    raw_neighborhood VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    available_monday  BOOLEAN NOT NULL,
    available_tuesday  BOOLEAN NOT NULL,
    available_wednesday  BOOLEAN NOT NULL,
    available_thursday  BOOLEAN NOT NULL,
    available_friday  BOOLEAN NOT NULL,
    available_saturday  BOOLEAN NOT NULL,
    available_sunday  BOOLEAN NOT NULL,
    available_weekday_mornings  BOOLEAN NOT NULL,
    available_weekday_afternoon  BOOLEAN NOT NULL,
    available_weekday_evening  BOOLEAN NOT NULL,
    available_weekend_mornings  BOOLEAN NOT NULL,
    available_weekend_afternoon  BOOLEAN NOT NULL,
    available_weekend_evening  BOOLEAN NOT NULL,
    street_team_status STREET_TEAM_STATUS,
    neighborhood_id INT NOT NULL,
    vehicle_id INT,
    source_id INT
);
CREATE TABLE partners(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    is_confirmed BOOLEAN NOT NULL,
    email_attendance BOOLEAN NOT NULL,
    attendance_contact_cc TEXT,
    attendance_contact_email TEXT,
    neighborhood_id INT,
    address VARCHAR(255),
    mission TEXT,
    contact_info TEXT,
    rsvp_link_required BOOLEAN NOT NUll,
    link VARCHAR(255),
    partner_city_harvest BOOLEAN NOT NULL,
    partner_citymeals BOOLEAN NOT NULL,
    partner_dycd BOOLEAN NOT NULL,
    geocode TEXT,
    landing_page_link VARCHAR(255),
    email_feedback BOOLEAN NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_not_public BOOLEAN NOT NUll
);
CREATE TABLE shifts(
    id SERIAL PRIMARY KEY,
    partner_id INTEGER,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    car_needed BOOLEAN NOT NULL,
    shift_type_id INT NOT NULL,
    notes TEXT,
    max_volunteers INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE assignments(
    id SERIAL PRIMARY KEY,
    volunteer_id INT NOT NULL,
    shift_id INT NOT NULL,
    confirmation_status_id INT NOT NULL,
    attendance_status_id INT,
    reminder_sent BOOLEAN NOT NULL,
    follow_up_sent BOOLEAN NOT NULL,
    expiration_sent BOOLEAN NOT NULL,
    decline_reason_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE feedback(
    id SERIAL PRIMARY KEY,
    volunteer_id INT,
    partner_id INT,
    body TEXT,
    notes TEXT,
    feedback_type FEEDBACK_TYPE NOT NULL,
    is_flex BOOLEAN NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE messages(
    id SERIAL PRIMARY KEY,
    volunteer_id INT,
    phone BIGINT NOT NULL,
    body TEXT NOT NULL,
    send_status VARCHAR(255)
);