jam-cloud/db/up/mixdown.sql

CREATE TABLE jam_track_mixdowns (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  jam_track_id VARCHAR(64) NOT NULL REFERENCES jam_tracks(id) ON DELETE CASCADE,
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  settings JSON NOT NULL,
  name VARCHAR(1000) NOT NULL,
  description VARCHAR(1000),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE jam_track_mixdown_packages (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  jam_track_mixdown_id VARCHAR(64) NOT NULL REFERENCES jam_track_mixdowns(id) ON DELETE CASCADE,
  file_type VARCHAR NOT NULL ,
  sample_rate INTEGER NOT NULL,
  url VARCHAR(2048),
  md5 VARCHAR,
  length INTEGER,
  downloaded_since_sign BOOLEAN NOT NULL DEFAULT FALSE,
  last_step_at TIMESTAMP,
  last_signed_at TIMESTAMP,
  download_count INTEGER NOT NULL DEFAULT 0,
  signed_at TIMESTAMP,
  downloaded_at TIMESTAMP,
  signing_queued_at TIMESTAMP,
  error_count INTEGER NOT NULL DEFAULT 0,
  error_reason VARCHAR,
  error_detail VARCHAR,
  should_retry BOOLEAN NOT NULL DEFAULT FALSE,
  packaging_steps INTEGER,
  current_packaging_step INTEGER,
  private_key VARCHAR,
  signed BOOLEAN,
  signing_started_at TIMESTAMP,
  first_downloaded TIMESTAMP,
  signing BOOLEAN NOT NULL DEFAULT FALSE,
  encrypt_type VARCHAR,
  first_downloaded_at TIMESTAMP,
  last_downloaded_at TIMESTAMP,
  version VARCHAR NOT NULL DEFAULT '1',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE jam_track_rights ADD COLUMN last_mixdown_id VARCHAR(64) REFERENCES jam_track_mixdowns(id) ON DELETE SET NULL;

ALTER TABLE notifications ADD COLUMN jam_track_mixdown_package_id VARCHAR(64) REFERENCES jam_track_mixdown_packages(id) ON DELETE CASCADE;

ALTER TABLE jam_track_mixdown_packages ADD COLUMN last_errored_at TIMESTAMP;
ALTER TABLE jam_track_mixdown_packages ADD COLUMN queued BOOLEAN DEFAULT FALSE;
ALTER TABLE jam_track_mixdown_packages ADD COLUMN speed_pitched BOOLEAN DEFAULT FALSE;
ALTER TABLE jam_track_rights ADD COLUMN queued BOOLEAN DEFAULT FALSE;

CREATE INDEX jam_track_rights_queued ON jam_track_rights(queued);
CREATE INDEX jam_track_rights_signing_queued ON jam_track_rights(signing_queued_at);
CREATE INDEX jam_track_rights_updated ON jam_track_rights(updated_at);

CREATE INDEX jam_track_mixdown_packages_queued ON jam_track_mixdown_packages(queued);
CREATE INDEX jam_track_mixdown_packages_signing_queued ON jam_track_mixdown_packages(signing_queued_at);
CREATE INDEX jam_track_mixdown_packages_updated ON jam_track_mixdown_packages(updated_at);