Data Model

Aludel stores prompts, executions, evaluations, and provider configuration in PostgreSQL. The schema stays structured where clarity matters and turns to JSONB where the work demands a more flexible vessel.

Schema Diagram

erDiagram
    projects ||--o{ prompts : contains
    projects ||--o{ suites : organizes
    prompts ||--o{ prompt_versions : snapshots
    prompts ||--o{ runs : executes
    prompts ||--o{ suites : validates
    runs ||--o{ run_results : produces
    providers ||--o{ run_results : powers
    providers ||--o{ suite_runs : evaluates_with
    suites ||--o{ test_cases : includes
    suites ||--o{ suite_runs : records
    test_cases ||--o{ test_case_documents : attaches

    projects {
        id id
        string name
        string description
    }

    prompts {
        id id
        id project_id
        string name
        string template
        string tags
        integer current_version
    }

    prompt_versions {
        id id
        id prompt_id
        integer version_number
        string template
        datetime inserted_at
    }

    runs {
        id id
        id prompt_id
        id prompt_version_id
        json variable_values
        string status
        datetime executed_at
    }

    run_results {
        id id
        id run_id
        id provider_id
        string response
        integer latency_ms
        integer tokens_input
        integer tokens_output
        decimal cost_usd
        string error
    }

    providers {
        id id
        string name
        string provider_type
        string model
        string api_endpoint
        json config
        boolean enabled
    }

    suites {
        id id
        id prompt_id
        id project_id
        string name
        string description
    }

    test_cases {
        id id
        id suite_id
        string name
        json variable_values
        json assertions
    }

    test_case_documents {
        id id
        id test_case_id
        string filename
        string content_type
        string file_path
        integer file_size
    }

    suite_runs {
        id id
        id suite_id
        id provider_id
        string status
        integer passed_count
        integer failed_count
        integer total_latency_ms
        decimal total_cost_usd
        datetime executed_at
    }

Loading

Core Entities

providers

field :provider_type, :string  # "openai", "anthropic", "ollama"
field :model, :string
field :config, :map            # JSONB
field :enabled, :boolean

Provider-specific settings are stored in JSONB, for example %{temperature: 0.7, max_tokens: 1000}.

prompts

field :template, :string        # "Summarize {{article}}"
field :tags, {:array, :string}  # ["summarization"]
belongs_to :project

prompt_versions

Immutable snapshots. Each update_prompt/2 creates a new version record, preserving the lineage of the prompt instead of overwriting its past.

field :version_number, :integer
field :template, :string
belongs_to :prompt

runs

field :variable_values, :map  # JSONB: {"article": "..."}
field :status, :string        # "pending", "running", "completed"
belongs_to :prompt
belongs_to :prompt_version

run_results

field :response, :string
field :latency_ms, :integer
field :tokens_input, :integer
field :tokens_output, :integer
field :cost_usd, :decimal
belongs_to :run
belongs_to :provider

Estimated cost formula:

(tokens_input / 1000 * input_price) + (tokens_output / 1000 * output_price)

suites

field :name, :string
field :description, :string
belongs_to :prompt
belongs_to :project  # optional - organizes suites into projects

Suites can optionally belong to a project for organizational purposes. When a project is deleted, the project_id is set to nil (not cascade delete).

test_cases

field :variable_values, :map       # JSONB
field :assertions, {:array, :map}  # JSONB array
belongs_to :suite

Assertion types: contains, regex, exact_match, json_field

test_case_documents

Supported attachments include PDFs, images (.jpg, .png), and text-like files (.txt, .csv, .json).

suite_runs

field :passed_count, :integer
field :failed_count, :integer
field :total_latency_ms, :integer
field :total_cost_usd, :decimal
belongs_to :suite
belongs_to :provider

Storage Characteristics

• JSONB fields keep provider config, variable payloads, and assertions flexible
• Composite indexes support prompt history and provider-result lookups
• Window functions power percentile metrics such as P50 and P95 latency
• Cascading deletes keep prompt-related data consistent across versions, runs, and suites

Query Examples

# Prompt with latest version
Prompt
|> Repo.get!(id)
|> Repo.preload(versions: from(v in PromptVersion,
     order_by: [desc: v.version_number], limit: 1))

# Run with results
Run |> Repo.get!(id) |> Repo.preload([results: :provider])

# Evolution metrics
Aludel.Prompts.Evolution.get_evolution(prompt_id)