PromptGuard AI

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE schemas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  version INT NOT NULL DEFAULT 1,
  definition_json JSONB NOT NULL, -- The JSON Schema definition
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE (project_id, version)
);
CREATE INDEX idx_schemas_project_id ON schemas(project_id);

CREATE TABLE prompts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  schema_id UUID REFERENCES schemas(id) ON DELETE SET NULL, -- Link to a specific schema version
  version INT NOT NULL DEFAULT 1,
  name TEXT NOT NULL,
  content_template TEXT NOT NULL, -- The prompt text with placeholders
  model_settings_json JSONB, -- e.g., { "model": "gpt-4o", "temperature": 0.7 }
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE (project_id, name, version)
);
CREATE INDEX idx_prompts_project_id ON prompts(project_id);
CREATE INDEX idx_prompts_schema_id ON prompts(schema_id);

CREATE TABLE test_cases (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  prompt_id UUID REFERENCES prompts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  input_text TEXT NOT NULL, -- The input text to feed the prompt
  expected_output_json JSONB, -- Optional: for comparing with parsed LLM output
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_test_cases_prompt_id ON test_cases(prompt_id);

CREATE TYPE validation_status AS ENUM ('success', 'schema_mismatch', 'content_mismatch', 'llm_error', 'timeout');

CREATE TABLE test_runs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  test_case_id UUID REFERENCES test_cases(id) ON DELETE CASCADE,
  prompt_version_id UUID REFERENCES prompts(id) ON DELETE CASCADE, -- Snapshot of prompt used
  schema_version_id UUID REFERENCES schemas(id) ON DELETE CASCADE, -- Snapshot of schema used
  llm_model_used TEXT NOT NULL,
  raw_llm_output TEXT, -- The raw text output from the LLM
  parsed_output_json JSONB, -- The parsed JSON from LLM output
  validation_status validation_status NOT NULL,
  error_details TEXT, -- Details if validation failed or LLM error
  duration_ms INT,
  cost_usd NUMERIC(10, 6),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_test_runs_test_case_id ON test_runs(test_case_id);
CREATE INDEX idx_test_runs_prompt_version_id ON test_runs(prompt_version_id);

CREATE TABLE api_keys (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  key_hash TEXT NOT NULL UNIQUE, -- Hashed API key
  name TEXT NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  last_used_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_api_keys_organization_id ON api_keys(organization_id);