No-Code DataSync

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  encrypted_password TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE workspaces (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_workspaces_user_id ON workspaces(user_id);

CREATE TYPE INTEGRATION_TYPE AS ENUM ('airtable', 'webflow', 'notion');
CREATE TABLE integrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  type INTEGRATION_TYPE NOT NULL,
  name TEXT NOT NULL, -- User-defined name for this integration
  config JSONB NOT NULL, -- Stores API key (encrypted) or OAuth tokens, base IDs, etc.
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_integrations_workspace_id ON integrations(workspace_id);

CREATE TYPE DATASOURCE_TYPE AS ENUM ('table', 'collection', 'database');
CREATE TABLE datasources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id UUID REFERENCES integrations(id) ON DELETE CASCADE,
  name TEXT NOT NULL, -- Name from the external tool (e.g., 'Products' table)
  type DATASOURCE_TYPE NOT NULL,
  identifier TEXT NOT NULL, -- External ID (e.g., Airtable table ID, Webflow Collection ID)
  schema JSONB, -- Stores fetched schema/fields from the external tool
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(integration_id, identifier) -- Ensures unique datasource per integration
);

CREATE INDEX idx_datasources_integration_id ON datasources(integration_id);

CREATE TYPE SYNC_FREQUENCY AS ENUM ('hourly', 'daily', 'weekly', 'realtime');
CREATE TYPE SYNC_STATUS AS ENUM ('active', 'paused', 'error');
CREATE TABLE sync_configs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  source_id UUID REFERENCES datasources(id) ON DELETE CASCADE,
  target_id UUID REFERENCES datasources(id) ON DELETE CASCADE,
  mapping JSONB NOT NULL, -- Stores field-to-field mapping and transformation rules
  frequency SYNC_FREQUENCY NOT NULL,
  status SYNC_STATUS NOT NULL DEFAULT 'active',
  last_run_at TIMESTAMPTZ,
  next_run_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sync_configs_workspace_id ON sync_configs(workspace_id);
CREATE INDEX idx_sync_configs_source_id ON sync_configs(source_id);
CREATE INDEX idx_sync_configs_target_id ON sync_configs(target_id);

CREATE TYPE SYNC_RUN_STATUS AS ENUM ('pending', 'running', 'completed', 'failed');
CREATE TABLE sync_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sync_config_id UUID REFERENCES sync_configs(id) ON DELETE CASCADE,
  status SYNC_RUN_STATUS NOT NULL DEFAULT 'pending',
  started_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  log JSONB, -- Stores detailed run logs, errors, etc.
  records_processed INT DEFAULT 0,
  records_created INT DEFAULT 0,
  records_updated INT DEFAULT 0,
  errors_count INT DEFAULT 0
);

CREATE INDEX idx_sync_runs_sync_config_id ON sync_runs(sync_config_id);

-- Stripe subscription info
CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT UNIQUE NOT NULL,
  stripe_subscription_id TEXT UNIQUE,
  status TEXT NOT NULL, -- 'active', 'canceled', 'past_due'
  current_period_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);