workspace_template.sql

-- Your SQL goes here
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
--
-- Name: {replaceme}; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA IF NOT EXISTS {replaceme};

--
-- Name: event_logger(); Type: FUNCTION; Schema: {replaceme}; Owner: -
--
CREATE OR REPLACE FUNCTION {replaceme}.event_logger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    old_data json;
    new_data json;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        old_data := row_to_json(OLD);
        new_data := row_to_json(NEW);
        INSERT INTO {replaceme}.event_log
            (table_name, user_name, action, original_data, new_data, query)
            VALUES (
                TG_TABLE_NAME::TEXT,
                session_user::TEXT,
                TG_OP,
                old_data,
                new_data,
                current_query()
            );
    ELSIF (TG_OP = 'DELETE') THEN
        old_data := row_to_json(OLD);
        INSERT INTO {replaceme}.event_log
            (table_name, user_name, action, original_data, query)
            VALUES (
                TG_TABLE_NAME::TEXT,
                session_user::TEXT,
                TG_OP,
                old_data,
                current_query()
            );
    ELSIF (TG_OP = 'INSERT') THEN
        new_data = row_to_json(NEW);
        INSERT INTO {replaceme}.event_log
            (table_name, user_name, action, new_data, query)
            VALUES (
                TG_TABLE_NAME::TEXT,
                session_user::TEXT,
                TG_OP,
                new_data,
                current_query()
            );
    END IF;
    RETURN NULL;
END;
$$;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: contexts; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.contexts  (
    id character varying PRIMARY KEY,
    value json NOT NULL,
    override_id character varying NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by character varying NOT NULL,
    priority integer DEFAULT 1 NOT NULL,
    override json DEFAULT '{}'::json NOT NULL
);
--
-- Name: default_configs; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.default_configs  (
    key character varying PRIMARY KEY,
    value json NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by character varying NOT NULL,
    schema json DEFAULT '{}'::json NOT NULL
);
--
-- Name: dimensions; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.dimensions (
    dimension character varying PRIMARY KEY,
    priority integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by character varying NOT NULL,
    schema json DEFAULT '{}'::json NOT NULL
);
--
-- Name: event_log; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.event_log (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    table_name text NOT NULL,
    user_name text NOT NULL,
    "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    action text NOT NULL,
    original_data json,
    new_data json,
    query text NOT NULL,
    PRIMARY KEY(id, timestamp)
) PARTITION BY RANGE ("timestamp");

--
-- Name: event_log_action_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS event_log_action_index ON ONLY {replaceme}.event_log USING btree (action) INCLUDE ("timestamp", table_name);
--
-- Name: event_log_table_name_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS event_log_table_name_index ON ONLY {replaceme}.event_log USING btree (table_name) INCLUDE (action, "timestamp");
--
-- Name: event_log_timestamp_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS event_log_timestamp_index ON ONLY {replaceme}.event_log USING btree ("timestamp") INCLUDE (action, table_name);

--
-- Name: contexts contexts_audit; Type: TRIGGER; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE TRIGGER contexts_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.contexts FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
--
-- Name: default_configs default_configs_audit; Type: TRIGGER; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE TRIGGER default_configs_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.default_configs FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
--
-- Name: dimensions dimensions_audit; Type: TRIGGER; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE TRIGGER dimensions_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.dimensions FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
-- Your SQL goes here
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
--
-- Name: {replaceme}; Type: SCHEMA; Schema: -; Owner: -
--
-- Name: experiment_status_type; Type: TYPE; Schema: {replaceme}; Owner: -
--

--
-- Name: not_null_text; Type: DOMAIN; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE DOMAIN {replaceme}.not_null_text AS text NOT NULL;
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
--
-- Name: event_logger(); Type: FUNCTION; Schema: {replaceme}; Owner: -
--

DO $$ BEGIN
    CREATE TYPE public.experiment_status_type AS ENUM (
        'CREATED',
        'CONCLUDED',
        'INPROGRESS'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
--
-- Name: experiments; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.experiments (
    id bigint PRIMARY KEY,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by text NOT NULL,
    last_modified timestamp with time zone DEFAULT now() NOT NULL,
    name text NOT NULL,
    override_keys {replaceme}.not_null_text [] NOT NULL,
    status public.experiment_status_type NOT NULL,
    traffic_percentage integer NOT NULL,
    context json NOT NULL,
    variants json NOT NULL,
    last_modified_by text DEFAULT 'Null'::text NOT NULL,
    chosen_variant text,
    CONSTRAINT experiments_traffic_percentage_check CHECK ((traffic_percentage >= 0))
);
--
-- Name: experiment_created_date_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS experiment_created_date_index ON {replaceme}.experiments USING btree (created_at) INCLUDE (id);
--
-- Name: experiment_last_modified_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS experiment_last_modified_index ON {replaceme}.experiments USING btree (last_modified) INCLUDE (id, created_at);
--
-- Name: experiment_status_index; Type: INDEX; Schema: {replaceme}; Owner: -
--
CREATE INDEX IF NOT EXISTS experiment_status_index ON {replaceme}.experiments USING btree (status) INCLUDE (created_at, last_modified);
--
-- Name: experiments experiments_audit; Type: TRIGGER; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE TRIGGER experiments_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.experiments FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

------------ Parititions for 2025 -----------
CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m01 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m02 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m03 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-03-01') TO ('2025-04-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m04 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-04-01') TO ('2025-05-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m05 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-05-01') TO ('2025-06-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m06 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-06-01') TO ('2025-07-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m07 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-07-01') TO ('2025-08-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m08 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-08-01') TO ('2025-09-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m09 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-09-01') TO ('2025-10-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m10 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m11 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-11-01') TO ('2025-12-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2025m12 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2025-12-01') TO ('2026-01-01');

------------ Parititions for 2026 -----------
CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m01 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m02 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m03 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m04 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m05 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m06 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m07 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m08 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m09 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m10 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m11 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE IF NOT EXISTS {replaceme}.event_log_y2026m12 PARTITION OF {replaceme}.event_log FOR
VALUES
FROM ('2026-12-01') TO ('2027-01-01');

-- Your SQL goes here
-- Name: functions; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.functions (
    function_name text PRIMARY KEY,
    published_code text,
    draft_code text NOT NULL,
    function_description text NOT NULL,
    published_runtime_version VARCHAR(16),
    draft_runtime_version VARCHAR(16) NOT NULL,
    published_at timestamp without time zone,
    draft_edited_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    published_by text,
    draft_edited_by text NOT NULL
);
--
-- Name: functions functions_audit; Type: TRIGGER; Schema: {replaceme}; Owner: -
--
DO $$ BEGIN
    CREATE TRIGGER functions_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.functions FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
-- Your SQL goes here
DO $$ BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'value_validation_function_name'
    ) THEN
        ALTER TABLE {replaceme}.dimensions ADD COLUMN IF NOT EXISTS function_name text NULL;
        
        ALTER TABLE {replaceme}.dimensions ADD FOREIGN KEY(function_name) REFERENCES {replaceme}.functions(function_name);
    END IF;
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND column_name = 'value_validation_function_name'
    ) THEN
        ALTER TABLE {replaceme}.default_configs ADD COLUMN IF NOT EXISTS function_name text NULL;
        
        ALTER TABLE {replaceme}.default_configs ADD FOREIGN KEY(function_name) REFERENCES {replaceme}.functions(function_name);
    END IF;
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
-- Your SQL goes here
-- Name: functions; Type: TABLE; Schema: {replaceme}; Owner: -
--
CREATE TABLE IF NOT EXISTS {replaceme}.config_versions (
id bigint PRIMARY KEY,
config json NOT NULL,
config_hash TEXT NOT NULL,
tags varchar(100) [] check (array_position(tags, null) is null),
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS config_verions_tags_index ON {replaceme}.config_versions USING gin(tags);
CREATE INDEX IF NOT EXISTS config_versions_id_index ON {replaceme}.config_versions(id);
-- Your SQL goes here
CREATE TABLE IF NOT EXISTS {replaceme}.type_templates (
    type_name TEXT PRIMARY KEY,
    type_schema JSON NOT NULL,
    created_by TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS type_templates_index ON {replaceme}.type_templates(type_name);
CREATE INDEX IF NOT EXISTS type_templates_created_at_index ON {replaceme}.type_templates(created_at);

DO $$ BEGIN
    IF EXISTS(SELECT *
        FROM information_schema.columns
        WHERE table_name='type_templates' and table_schema='{replaceme}' and column_name='last_modified')
  THEN
    CREATE INDEX IF NOT EXISTS type_templates_last_modifed_index ON {replaceme}.type_templates(last_modified);
  END IF;
END $$;
-- Your SQL goes here

ALTER TABLE {replaceme}.functions
ADD COLUMN IF NOT EXISTS last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
ADD COLUMN IF NOT EXISTS last_modified_by varchar(200) not null default('null');

ALTER TABLE {replaceme}.dimensions
ADD COLUMN IF NOT EXISTS last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
ADD COLUMN IF NOT EXISTS last_modified_by varchar(200) not null default('null');

ALTER TABLE {replaceme}.contexts
ADD COLUMN IF NOT EXISTS last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
ADD COLUMN IF NOT EXISTS last_modified_by varchar(200) not null default('null');

ALTER TABLE {replaceme}.default_configs
ADD COLUMN IF NOT EXISTS last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
ADD COLUMN IF NOT EXISTS last_modified_by varchar(200) not null default('null');

DO $$ BEGIN
    IF EXISTS(SELECT *
        FROM information_schema.columns
        WHERE table_name='type_templates' and table_schema='{replaceme}' and column_name='last_modified')
  THEN
    ALTER TABLE {replaceme}.type_templates rename column last_modified to last_modified_at;
  END IF;
END $$;

ALTER TABLE {replaceme}.type_templates
ADD COLUMN IF NOT EXISTS last_modified_by varchar(200) not null default('null');
-- Your SQL goes here
ALTER TABLE {replaceme}.dimensions
ADD COLUMN IF NOT EXISTS position integer DEFAULT 0 NOT NULL;
ALTER TABLE {replaceme}.contexts
ADD COLUMN IF NOT EXISTS weight numeric(1000, 0) DEFAULT 1 NOT NULL;
CREATE INDEX IF NOT EXISTS idx_contexts_weight ON {replaceme}.contexts(weight);
-- Your SQL goes here
ALTER TABLE {replaceme}.dimensions
ALTER COLUMN priority SET DEFAULT 1;

DO $$ BEGIN
    ALTER TABLE {replaceme}.dimensions
    ADD CONSTRAINT dimension_unique_position UNIQUE (position) DEFERRABLE INITIALLY DEFERRED;
EXCEPTION
    WHEN duplicate_table THEN
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.contexts ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;
ALTER TABLE {replaceme}.contexts ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

ALTER TABLE {replaceme}.dimensions ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;
ALTER TABLE {replaceme}.dimensions ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

ALTER TABLE {replaceme}.default_configs ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;
ALTER TABLE {replaceme}.default_configs ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

ALTER TABLE {replaceme}.type_templates ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;
ALTER TABLE {replaceme}.type_templates ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

DO $$ BEGIN
    IF EXISTS(SELECT *
        FROM information_schema.columns
        WHERE table_name='functions' and table_schema='{replaceme}' and column_name='function_description')
  THEN
    ALTER TABLE {replaceme}.functions RENAME COLUMN function_description TO description;
  END IF;
END $$;

ALTER TABLE {replaceme}.functions ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

ALTER TABLE {replaceme}.config_versions ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;

ALTER TABLE {replaceme}.experiments ADD COLUMN IF NOT EXISTS description TEXT NOT NULL;
ALTER TABLE {replaceme}.experiments ADD COLUMN IF NOT EXISTS change_reason TEXT NOT NULL;

DO $$ BEGIN
    IF NOT EXISTS(SELECT *
        FROM {replaceme}.dimensions
        WHERE dimension = 'variantIds')
    THEN
    INSERT INTO {replaceme}.dimensions (
            dimension,
            priority,
            created_at,
            created_by,
            schema,
            function_name,
            description,
            change_reason
        )
    VALUES (
            'variantIds',
            0,
            CURRENT_TIMESTAMP,
            'default@superposition.io',
            '{"type": "string","pattern": ".*"}'::json,
            null,
            'variantIds are used by experimentation module to manage and select variations',
            'initial setup'
    );
    END IF;
END $$;

INSERT INTO {replaceme}.type_templates(type_name, type_schema, created_by, created_at, last_modified_by, last_modified_at, description, change_reason)
VALUES (
        'Number',
        '{"type": "integer"}',
        'user@superposition.io',
        NOW(),
        'user@superposition.io',
        NOW(),
        'Number type is used to represent numeric values',
        'initial setup'
    ),
    (
        'Decimal',
        '{"type": "number"}',
        'user@superposition.io',
        NOW(),
        'user@superposition.io',
        NOW(),
        'Decimal type is used to represent decimal values',
        'initial setup'
    ),
    (
        'Boolean',
        '{"type": "boolean"}',
        'user@superposition.io',
        NOW(),
        'user@superposition.io',
        NOW(),
        'Boolean type is used to represent true/false values',
        'initial setup'
    ),
    (
        'Enum',
        '{"type": "string", "enum": ["android", "ios"]}',
        'user@superposition.io',
        NOW(),
        'user@superposition.io',
        NOW(),
        'Enum type is used to represent a fixed set of values',
        'initial setup'
    ),
    (
        'Pattern',
        '{"type": "string", "pattern": ".*"}',
        'user@superposition.io',
        NOW(),
        'user@superposition.io',
        NOW(),
        'Pattern type is used to represent a string that matches a specific pattern',
        'initial setup'
    ) ON CONFLICT DO NOTHING;

ALTER TYPE public.experiment_status_type ADD VALUE IF NOT EXISTS 'DISCARDED';

DO $$ BEGIN
    CREATE TYPE public.function_types AS ENUM (
    'VALIDATION',
    'AUTOCOMPLETE'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.functions ADD COLUMN IF NOT EXISTS function_type public.FUNCTION_TYPES NOT NULL DEFAULT 'VALIDATION';

ALTER TABLE {replaceme}.dimensions
ADD COLUMN IF NOT EXISTS dependency_graph JSON default '{}'::json NOT NULL,
ADD COLUMN IF NOT EXISTS dependents TEXT[] default '{}' NOT NULL,
ADD COLUMN IF NOT EXISTS dependencies TEXT[] default '{}' NOT NULL;


DO $$ BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'value_compute_function_name'
    ) THEN
        ALTER TABLE {replaceme}.dimensions ADD COLUMN IF NOT EXISTS autocomplete_function_name text NULL;
        
        ALTER TABLE {replaceme}.dimensions ADD FOREIGN KEY(autocomplete_function_name) REFERENCES {replaceme}.functions(function_name);
    END IF;
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND column_name = 'value_compute_function_name'
    ) THEN
        ALTER TABLE {replaceme}.default_configs ADD COLUMN IF NOT EXISTS autocomplete_function_name text NULL;
        
        ALTER TABLE {replaceme}.default_configs ADD FOREIGN KEY(autocomplete_function_name) REFERENCES {replaceme}.functions(function_name);
    END IF;
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE public.http_method AS ENUM (
        'GET',
        'PUT',
        'POST',
        'DELETE',
        'PATCH',
        'HEAD'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS {replaceme}.webhooks (
    name text PRIMARY KEY,
    description text NOT NULL,
    enabled boolean NOT NULL,
    url text NOT NULL,
    method public.http_method NOT NULL DEFAULT 'POST',
    payload_version text NOT NULL,
    custom_headers json NOT NULL DEFAULT '{}'::json,
    events text[] NOT NULL,
    max_retries integer NOT NULL DEFAULT 0,
    last_triggered_at timestamp,
    change_reason TEXT NOT NULL,
    created_by text NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_modified_by text NOT NULL,
    last_modified_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DO $$ BEGIN
    CREATE TRIGGER webhooks_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.webhooks FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.experiments
ADD COLUMN IF NOT EXISTS metrics JSON DEFAULT '{"enabled": false}'::json NOT NULL,
ADD COLUMN IF NOT EXISTS started_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS started_by TEXT;

ALTER TYPE public.experiment_status_type ADD VALUE IF NOT EXISTS 'PAUSED';

DO $$ BEGIN
    CREATE TYPE public.experiment_type AS ENUM (
        'DEFAULT',
        'DELETE_OVERRIDES'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.experiments ADD COLUMN IF NOT EXISTS experiment_type public.experiment_type NOT NULL DEFAULT 'DEFAULT';

CREATE TABLE IF NOT EXISTS {replaceme}.experiment_groups(
    id bigint PRIMARY KEY,
    context_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    change_reason TEXT NOT NULL,
    context JSON NOT NULL,
    traffic_percentage integer NOT NULL CONSTRAINT traffic_percentage_range CHECK (traffic_percentage >= 0 AND traffic_percentage <= 100),
    member_experiment_ids bigint[] NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by TEXT NOT NULL,
    last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified_by TEXT NOT NULL
);

ALTER TABLE {replaceme}.experiments ADD COLUMN IF NOT EXISTS experiment_group_id bigint;

DO $$ BEGIN
    CREATE TRIGGER experiment_groups_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.experiment_groups FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.experiment_groups ADD COLUMN IF NOT EXISTS buckets JSON[] DEFAULT array_fill(NULL::JSON, ARRAY[100]) NOT NULL CHECK (cardinality(buckets) = 100);

DO $$ BEGIN
    CREATE TYPE public.group_type AS ENUM (
        'USER_CREATED',
        'SYSTEM_GENERATED'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

ALTER TABLE {replaceme}.experiment_groups
ADD COLUMN IF NOT EXISTS group_type public.group_type DEFAULT 'USER_CREATED';

ALTER TABLE {replaceme}.functions
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS created_by TEXT NOT NULL DEFAULT 'NOT AVAILABLE';

-- drop the constraint so validations work, this for backfill
ALTER TABLE {replaceme}.functions ALTER COLUMN created_by DROP DEFAULT;

UPDATE {replaceme}.functions SET created_at = last_modified_at WHERE created_at IS NULL;

-- set constraints on created_at after backfill is done
ALTER TABLE {replaceme}.functions
ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP,
ALTER COLUMN created_at SET NOT NULL;

ALTER TABLE {replaceme}.dimensions
ADD COLUMN IF NOT EXISTS dimension_type TEXT NOT NULL DEFAULT 'REGULAR';

ALTER TABLE {replaceme}.dimensions
    DROP COLUMN IF EXISTS dependencies,
    DROP COLUMN IF EXISTS dependents;

CREATE TABLE IF NOT EXISTS {replaceme}.variables (
    name VARCHAR PRIMARY KEY,
    value TEXT NOT NULL,
    description TEXT NOT NULL,
    change_reason TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by VARCHAR NOT NULL,
    last_modified_by VARCHAR NOT NULL
);

-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_variables_created_at ON {replaceme}.variables(created_at);
CREATE INDEX IF NOT EXISTS idx_variables_last_modified_at ON {replaceme}.variables(last_modified_at);

-- Add audit trigger for the variables table
DO $$ BEGIN
    CREATE TRIGGER variables_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.variables FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE public.function_types_new AS ENUM (
        'VALUE_VALIDATION',
        'VALUE_COMPUTE',
        'CONTEXT_VALIDATION',
        'CHANGE_REASON_VALIDATION'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    -- Check if column type needs conversion
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'functions' 
        AND column_name = 'function_type'
        AND udt_name = 'function_types' -- old type
    ) THEN
        ALTER TABLE {replaceme}.functions
          ALTER COLUMN function_type DROP DEFAULT,
          ALTER COLUMN function_type TYPE public.function_types_new
          USING CASE function_type::text
                  WHEN 'VALIDATION' THEN 'VALUE_VALIDATION'::public.function_types_new
                  WHEN 'AUTOCOMPLETE' THEN 'VALUE_COMPUTE'::public.function_types_new
                END,
          ALTER COLUMN function_type SET DEFAULT 'VALUE_VALIDATION'::public.function_types_new;
    END IF;
END $$;

INSERT INTO {replaceme}.functions (
    function_name,
    published_code,
    draft_code,
    description,
    published_runtime_version,
    draft_runtime_version,
    published_at,
    draft_edited_at,
    published_by,
    draft_edited_by,
    last_modified_at,
    last_modified_by,
    change_reason,
    function_type,
    created_by,
    created_at
) VALUES (
    'context_validation',
    'YXN5bmMgZnVuY3Rpb24gZXhlY3V0ZShwYXlsb2FkKSB7CiAgICBjb25zdCB7IGNvbnRleHRfdmFsaWRhdGUgfSA9IHBheWxvYWQ7CiAgICBjb25zdCB7IGVudmlyb25tZW50IH0gPSBjb250ZXh0X3ZhbGlkYXRlOwoKICAgIC8vIHZhbGlkYXRpb24gbG9naWMgZ29lcyBoZXJlCgogICAgcmV0dXJuIHRydWU7Cn0K',
    'YXN5bmMgZnVuY3Rpb24gZXhlY3V0ZShwYXlsb2FkKSB7CiAgICBjb25zdCB7IGNvbnRleHRfdmFsaWRhdGUgfSA9IHBheWxvYWQ7CiAgICBjb25zdCB7IGVudmlyb25tZW50IH0gPSBjb250ZXh0X3ZhbGlkYXRlOwoKICAgIC8vIHZhbGlkYXRpb24gbG9naWMgZ29lcyBoZXJlCgogICAgcmV0dXJuIHRydWU7Cn0K',
    'Validates the entire context object for the whole workspace before processing. Returns true if context is valid, false otherwise.',
    '1.0',
    '1.0',
    NOW(),
    NOW(),
    'user@superposition.io',
    'user@superposition.io',
    NOW(),
    'user@superposition.io',
    'Initial creation of context validation function',
    'CONTEXT_VALIDATION',
    'user@superposition.io',
    NOW()
) ON CONFLICT DO NOTHING;

INSERT INTO {replaceme}.functions (
    function_name,
    published_code,
    draft_code,
    description,
    published_runtime_version,
    draft_runtime_version,
    published_at,
    draft_edited_at,
    published_by,
    draft_edited_by,
    last_modified_at,
    last_modified_by,
    change_reason,
    function_type,
    created_by,
    created_at
) VALUES (
    'change_reason_validation',
    'YXN5bmMgZnVuY3Rpb24gZXhlY3V0ZShwYXlsb2FkKSB7CiAgICBjb25zdCB7IGNoYW5nZV9yZWFzb25fdmFsaWRhdGUgfSA9IHBheWxvYWQ7CiAgICBjb25zdCB7IGNoYW5nZV9yZWFzb24gfSA9IGNoYW5nZV9yZWFzb25fdmFsaWRhdGU7CgogICAgLy8gdmFsaWRhdGlvbiBsb2dpYyBnb2VzIGhlcmUKICAgIAogICAgcmV0dXJuIHRydWU7Cn0K',
    'YXN5bmMgZnVuY3Rpb24gZXhlY3V0ZShwYXlsb2FkKSB7CiAgICBjb25zdCB7IGNoYW5nZV9yZWFzb25fdmFsaWRhdGUgfSA9IHBheWxvYWQ7CiAgICBjb25zdCB7IGNoYW5nZV9yZWFzb24gfSA9IGNoYW5nZV9yZWFzb25fdmFsaWRhdGU7CgogICAgLy8gdmFsaWRhdGlvbiBsb2dpYyBnb2VzIGhlcmUKICAgIAogICAgcmV0dXJuIHRydWU7Cn0K',
    'Validates the change reason for any creation/updation. Returns true if context is valid, false otherwise.',
    '1.0',
    '1.0',
    NOW(),
    NOW(),
    'user@superposition.io',
    'user@superposition.io',
    NOW(),
    'user@superposition.io',
    'Initial creation of change reason validation function',
    'CHANGE_REASON_VALIDATION',
    'user@superposition.io',
    NOW()
) ON CONFLICT DO NOTHING;

ALTER TABLE {replaceme}.dimensions
DROP CONSTRAINT IF EXISTS dimensions_autocomplete_function_name_fkey;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'autocomplete_function_name'
    ) THEN
        ALTER TABLE {replaceme}.dimensions
        RENAME COLUMN autocomplete_function_name TO value_compute_function_name;
    END IF;
END $$;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'value_compute_function_name'
    ) AND NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND constraint_name = 'dimensions_value_compute_function_name_fkey'
    ) THEN
        ALTER TABLE {replaceme}.dimensions
        ADD CONSTRAINT dimensions_value_compute_function_name_fkey
        FOREIGN KEY (value_compute_function_name)
        REFERENCES {replaceme}.functions(function_name);
    END IF;
END $$;


ALTER TABLE {replaceme}.default_configs
DROP CONSTRAINT IF EXISTS default_configs_autocomplete_function_name_fkey;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND column_name = 'autocomplete_function_name'
    ) THEN
        ALTER TABLE {replaceme}.default_configs
        RENAME COLUMN autocomplete_function_name TO value_compute_function_name;
    END IF;
END $$;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = '{replaceme}'
        AND table_name = 'default_configs'
        AND column_name = 'value_compute_function_name'
    ) AND NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_schema = '{replaceme}'
        AND table_name = 'default_configs'
        AND constraint_name = 'default_configs_value_compute_function_name_fkey'
    ) THEN
        ALTER TABLE {replaceme}.default_configs
        ADD CONSTRAINT default_configs_value_compute_function_name_fkey
        FOREIGN KEY (value_compute_function_name)
        REFERENCES {replaceme}.functions(function_name);
    END IF;
END $$;


ALTER TABLE {replaceme}.dimensions
DROP CONSTRAINT IF EXISTS dimensions_function_name_fkey;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'function_name'
    ) THEN
        ALTER TABLE {replaceme}.dimensions
        RENAME COLUMN function_name TO value_validation_function_name;
    END IF;
END $$;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND column_name = 'value_validation_function_name'
    ) AND NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'dimensions' 
        AND constraint_name = 'dimensions_value_validation_function_name_fkey'
    ) THEN
        ALTER TABLE {replaceme}.dimensions
        ADD CONSTRAINT dimensions_value_validation_function_name_fkey
        FOREIGN KEY (value_validation_function_name)
        REFERENCES {replaceme}.functions(function_name);
    END IF;
END $$;

ALTER TABLE {replaceme}.default_configs
DROP CONSTRAINT IF EXISTS default_configs_function_name_fkey;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND column_name = 'function_name'
    ) THEN
        ALTER TABLE {replaceme}.default_configs
        RENAME COLUMN function_name TO value_validation_function_name;
    END IF;
END $$;

DO $$ BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND column_name = 'value_validation_function_name'
    ) AND NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints 
        WHERE table_schema = '{replaceme}' 
        AND table_name = 'default_configs' 
        AND constraint_name = 'default_configs_value_validation_function_name_fkey'
    ) THEN
        ALTER TABLE {replaceme}.default_configs
        ADD CONSTRAINT default_configs_value_validation_function_name_fkey
        FOREIGN KEY (value_validation_function_name)
        REFERENCES {replaceme}.functions(function_name);
    END IF;
END $$;

UPDATE {replaceme}.functions
SET draft_runtime_version = '1.0'
WHERE draft_runtime_version = '1.0.0';

UPDATE {replaceme}.functions
SET published_runtime_version = '1.0'
WHERE published_runtime_version = '1.0.0';

CREATE TABLE IF NOT EXISTS {replaceme}.secrets (
    name VARCHAR PRIMARY KEY,
    encrypted_value TEXT NOT NULL,
    description TEXT NOT NULL,
    change_reason TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by TEXT NOT NULL,
    last_modified_by TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_secrets_created_at ON {replaceme}.secrets(created_at);
CREATE INDEX IF NOT EXISTS idx_secrets_last_modified_at ON {replaceme}.secrets(last_modified_at);

DO $$ BEGIN
    CREATE TRIGGER secrets_audit AFTER INSERT OR DELETE OR UPDATE ON {replaceme}.secrets FOR EACH ROW EXECUTE FUNCTION {replaceme}.event_logger();
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;