Feature flags and audit log

CREATE TYPE rule_operator AS ENUM ('equals', 'not_equals', 'in', 'contains', 'percentage');

CREATE TABLE projects (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    slug text NOT NULL UNIQUE
);

CREATE TABLE environments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    project_id uuid NOT NULL,
    name text NOT NULL,
    sdk_key text NOT NULL UNIQUE,
    UNIQUE (project_id, name)
);

CREATE TABLE flags (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    project_id uuid NOT NULL,
    key text NOT NULL,
    description text,
    archived boolean DEFAULT false NOT NULL,
    UNIQUE (project_id, key)
);

CREATE TABLE flag_variants (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    flag_id uuid NOT NULL,
    name text NOT NULL,
    value jsonb NOT NULL,
    UNIQUE (flag_id, name)
);

CREATE TABLE flag_states (
    flag_id uuid NOT NULL,
    environment_id uuid NOT NULL,
    enabled boolean DEFAULT false NOT NULL,
    default_variant_id uuid,
    PRIMARY KEY (flag_id, environment_id)
);
CREATE INDEX flag_states_environment_id_idx ON flag_states (environment_id);
CREATE INDEX flag_states_default_variant_id_idx ON flag_states (default_variant_id);

CREATE TABLE segments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    project_id uuid NOT NULL,
    name text NOT NULL,
    UNIQUE (project_id, name)
);

CREATE TABLE targeting_rules (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    flag_id uuid NOT NULL,
    environment_id uuid NOT NULL,
    segment_id uuid,
    attribute text,
    operator rule_operator NOT NULL,
    operand jsonb NOT NULL,
    variant_id uuid NOT NULL,
    position int NOT NULL
);
CREATE INDEX targeting_rules_flag_id_idx ON targeting_rules (flag_id);
CREATE INDEX targeting_rules_environment_id_idx ON targeting_rules (environment_id);
CREATE INDEX targeting_rules_segment_id_idx ON targeting_rules (segment_id);
CREATE INDEX targeting_rules_variant_id_idx ON targeting_rules (variant_id);

CREATE TABLE audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id uuid NOT NULL,
    actor text NOT NULL,
    action text NOT NULL,
    details jsonb DEFAULT '{}'::jsonb NOT NULL,
    at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX audit_log_project_idx ON audit_log (project_id, at);

ALTER TABLE environments ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE flags ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE flag_variants ADD FOREIGN KEY (flag_id) REFERENCES flags (id) ON DELETE CASCADE;

ALTER TABLE flag_states ADD FOREIGN KEY (flag_id) REFERENCES flags (id) ON DELETE CASCADE;

ALTER TABLE flag_states ADD FOREIGN KEY (environment_id) REFERENCES environments (id) ON DELETE CASCADE;

ALTER TABLE flag_states ADD FOREIGN KEY (default_variant_id) REFERENCES flag_variants (id);

ALTER TABLE segments ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE targeting_rules ADD FOREIGN KEY (flag_id) REFERENCES flags (id) ON DELETE CASCADE;

ALTER TABLE targeting_rules ADD FOREIGN KEY (environment_id) REFERENCES environments (id) ON DELETE CASCADE;

ALTER TABLE targeting_rules ADD FOREIGN KEY (segment_id) REFERENCES segments (id) ON DELETE CASCADE;

ALTER TABLE targeting_rules ADD FOREIGN KEY (variant_id) REFERENCES flag_variants (id);

ALTER TABLE audit_log ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;