Test Environment and Data Management Questions

def create_test_db_sql(base_name: str, run_id: str) -> str:
    """
    Returns an idempotent PostgreSQL SQL script to:
    - create a database for this test run
    - create schema, seeded table with deterministic rows
    - create limited role and grant privileges
    """
    # sanitize: allow only letters, digits, underscore, and start with letter
    import re
    def clean(s):
        s = re.sub(r'[^a-zA-Z0-9_]', '_', s)
        if not re.match(r'^[A-Za-z]', s):
            s = 't_' + s
        return s.lower()[:63]  # postgres identifier limit
    dbname = clean(f"{base_name}_{run_id}")
    role = clean(f"{base_name}_role_{run_id}")
    schema = 'test_schema'
    table = 'seed_data'
    sql = f"""
-- Create database if not exists (run as a superuser / existing connection to postgres)
DO $$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = '{dbname}') THEN
      PERFORM dblink_exec('dbname=postgres', 'CREATE DATABASE "{dbname}"');
   END IF;
END$$;

-- Connect to the test database
\\c {dbname}

-- Create schema if not exists
CREATE SCHEMA IF NOT EXISTS {schema};

-- Create table with primary key
CREATE TABLE IF NOT EXISTS {schema}.{table} (
  id   INT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Seed deterministic rows using upsert to ensure idempotency
INSERT INTO {schema}.{table} (id, name)
VALUES
  (1, 'alpha'),
  (2, 'beta'),
  (3, 'gamma')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
  WHERE {schema}.{table}.name IS DISTINCT FROM EXCLUDED.name;

-- Create limited role if not exists and set password placeholder (change in CI secrets)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '{role}') THEN
    CREATE ROLE {role} LOGIN PASSWORD 'changeme' NOSUPERUSER NOCREATEDB NOREPLICATION;
  END IF;
END$$;

-- Grant usage and limited privileges
GRANT USAGE ON SCHEMA {schema} TO {role};
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA {schema} TO {role};
ALTER DEFAULT PRIVILEGES IN SCHEMA {schema} GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO {role};
"""
    return sql