jam-cloud/db/up/cohorts.sql

CREATE TABLE cohorts (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),

    data_set JSON NOT NULL DEFAULT '{}',
    
    group_start TIMESTAMP NOT NULL,
    group_end TIMESTAMP NOT NULL,

    all_time  BOOLEAN NOT NULL DEFAULT FALSE,
    monthly_start TIMESTAMP,
    monthly_end TIMESTAMP,

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

CREATE INDEX index_group_date ON cohorts USING btree (group_start);

CREATE INDEX msuh_music_session_idx ON music_sessions_user_history USING btree(music_session_id);