PayloadPipe

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

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

CREATE TABLE webhook_endpoints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    incoming_path TEXT UNIQUE NOT NULL, -- Unique UUID path for the incoming webhook
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_webhook_endpoints_workspace_id ON webhook_endpoints(workspace_id);
CREATE UNIQUE INDEX idx_webhook_endpoints_incoming_path ON webhook_endpoints(incoming_path);

CREATE TABLE transformation_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    template_json JSONB NOT NULL, -- JSONB for reusable transformation logic snippets
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_transformation_templates_workspace_id ON transformation_templates(workspace_id);

CREATE TABLE pipes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    endpoint_id UUID NOT NULL REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    incoming_schema_sample JSONB, -- Sample payload for AI suggestions
    transformation_steps JSONB NOT NULL, -- Array of transformation steps (e.g., [{'type': 'rename', 'from': 'old', 'to': 'new'}])
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_pipes_endpoint_id ON pipes(endpoint_id);

CREATE TABLE destinations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pipe_id UUID NOT NULL REFERENCES pipes(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    method TEXT NOT NULL DEFAULT 'POST', -- e.g., POST, PUT
    headers JSONB DEFAULT '{}'::jsonb, -- Additional headers for outgoing request
    transformation_steps JSONB DEFAULT '[]'::jsonb, -- Additional transformations specific to this destination
    retry_config JSONB DEFAULT '{ "max_retries": 3, "delay_ms": 1000 }'::jsonb,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_destinations_pipe_id ON destinations(pipe_id);

CREATE TABLE webhook_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pipe_id UUID NOT NULL REFERENCES pipes(id) ON DELETE CASCADE,
    incoming_payload JSONB NOT NULL,
    transformed_payload JSONB,
    destination_response JSONB,
    status TEXT NOT NULL, -- e.g., 'received', 'transformed', 'delivered', 'failed'
    error_message TEXT,
    http_status_code INTEGER,
    attempt_count INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_webhook_logs_pipe_id_created_at ON webhook_logs(pipe_id, created_at DESC);