Postgres

-- One role for everything Traffical does
CREATE ROLE traffical LOGIN PASSWORD '<strong-random-password>';
GRANT CONNECT ON DATABASE your_database TO traffical;

-- Read access on your source data
GRANT USAGE ON SCHEMA analytics TO traffical;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO traffical;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO traffical;

-- A schema the pipeline owns. The pipeline issues CREATE SCHEMA IF NOT EXISTS
-- on first run, so granting CREATE on the database is enough. If you'd rather
-- pre-create the schema yourself, do that and grant USAGE + CREATE on it.
GRANT CREATE ON DATABASE your_database TO traffical;

CREATE SCHEMA traffical;
GRANT USAGE, CREATE ON SCHEMA traffical TO traffical;

-- Assignments: one row per (user, time, experiment, variant)
CREATE TABLE analytics.experiment_assignments (
  user_id          TEXT NOT NULL,
  assigned_at      TIMESTAMPTZ NOT NULL,
  experiment_name  TEXT NOT NULL,
  variant          TEXT NOT NULL
);
CREATE INDEX ON analytics.experiment_assignments (assigned_at);
CREATE INDEX ON analytics.experiment_assignments (user_id, experiment_name);

-- Outcomes: any events you want to measure
CREATE TABLE analytics.orders (
  order_id      TEXT PRIMARY KEY,
  user_id       TEXT NOT NULL,
  order_total   NUMERIC NOT NULL,
  currency      TEXT NOT NULL,
  event_time    TIMESTAMPTZ NOT NULL
);
CREATE INDEX ON analytics.orders (event_time);
CREATE INDEX ON analytics.orders (user_id);

SELECT
  user_id,
  assigned_at,
  experiment_name AS policy_key,
  variant         AS allocation_key
FROM analytics.experiment_assignments
WHERE assigned_at BETWEEN '{{start_date}}'::timestamptz
                      AND '{{end_date}}'::timestamptz

SELECT
  user_id,
  event_time,
  order_total
FROM analytics.orders
WHERE event_time BETWEEN '{{start_date}}'::timestamptz
                     AND '{{end_date}}'::timestamptz