CREATE USER my_user;
CREATE DATABASE Sift WITH owner = 'my_user';

CREATE TABLE organizations (
  organization_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);


CREATE TABLE assets (
  asset_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  organization_id UUID NOT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(organization_id)
);

CREATE TABLE channels (
  channel_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT,
  organization_id UUID NOT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(organization_id),
  asset_id UUID NOT NULL,
  FOREIGN KEY (asset_id) REFERENCES assets(asset_id)
);

-- Where assets refer to any hardware machine (rover, satellite, drone, car, etc.) that produces time-series telemetry data and channels refer to the asset's sensors that measure telemetry data. Examples of sensor data collected in channels include temperature, pressure, humidity, battery levels, stateful data, etc. More assets and/or sensors = more channels.