-- Structured datasets sent by the client (API today, file later).
-- A dataset is the CURRENT STATE of a client "table" (upsert), as opposed to
-- intake_items, which is an append-only staging queue. Without this split a
-- client that re-sends its whole catalog every night would accumulate infinite
-- history and the generated SQL view would return every record duplicated.
CREATE TABLE datasets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  record_key VARCHAR(190) NULL,
  fields JSON NULL,
  record_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_ingested_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_datasets_project_name (project_id, name),
  CONSTRAINT fk_datasets_project FOREIGN KEY (project_id) REFERENCES projects (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;