Calling an LLM API in a loop is the easy part of tracking how AI assistants talk about a brand. The part that decides whether the data is still useful in six months is the data model.
Homegrown "AI visibility" trackers tend to fall apart for the same reasons: prompts got edited in place, the model changed under them, raw answers weren't stored, and nobody could say why last month's numbers didn't match this month's.
This post lays out a schema and a scheduler that avoid those problems. It uses SQLite and GitHub Actions because both are free and easy to inspect. The design carries over to Postgres or BigQuery.
Requirements before tables
Write down what you'll need to answer later, and design for that:
- "What exactly did it say?" Every metric must link back to the raw answer text and its sources.
- "Is this change real?" You need multiple samples per prompt per day, not one.
- "Did we change, or did the engine change?" Model versions and prompt wording must be recorded, never overwritten.
- "How do we compare to competitors?" Brand detection must run for several brands over the same answers.
- "Can we re-score history?" When the detection logic improves, you should be able to re-run it over stored answers without paying for new API calls.
Requirement 5 is the one most people miss, and it's the reason to keep collection and scoring in separate tables.
The schema
-- What we ask. Prompts are immutable: editing wording creates a new row.
CREATE TABLE prompt (
id INTEGER PRIMARY KEY,
text TEXT NOT NULL UNIQUE,
topic TEXT, -- e.g. "pricing", "alternatives"
intent_stage TEXT CHECK (intent_stage IN
('problem','solution','comparison','decision')),
active INTEGER NOT NULL DEFAULT 1,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
-- Brands we score against every answer: ours and competitors'.
CREATE TABLE brand (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
is_ours INTEGER NOT NULL DEFAULT 0,
aliases TEXT NOT NULL, -- JSON array of regexes
domains TEXT NOT NULL -- JSON array of hostnames
);
-- One API call = one run. Raw output is stored verbatim.
CREATE TABLE run (
id INTEGER PRIMARY KEY,
prompt_id INTEGER NOT NULL REFERENCES prompt(id),
engine TEXT NOT NULL, -- 'openai', 'perplexity', 'gemini', ...
model TEXT NOT NULL, -- exact model string returned by the API
sample_idx INTEGER NOT NULL, -- 0..N-1 within the day
run_date TEXT NOT NULL, -- UTC date, used for daily grouping
answer_text TEXT NOT NULL,
sources TEXT NOT NULL, -- JSON array of URLs
raw_json TEXT, -- full API response, for future parsing
latency_ms INTEGER,
UNIQUE (prompt_id, engine, run_date, sample_idx)
);
-- Derived. Safe to DELETE and rebuild from `run` at any time.
CREATE TABLE score (
run_id INTEGER NOT NULL REFERENCES run(id),
brand_id INTEGER NOT NULL REFERENCES brand(id),
scorer_version TEXT NOT NULL, -- bump when detection logic changes
mentioned INTEGER NOT NULL,
position INTEGER, -- list position, NULL if not in a list
cited INTEGER NOT NULL,
sentiment TEXT, -- 'positive' | 'neutral' | 'critical'
PRIMARY KEY (run_id, brand_id, scorer_version)
);
CREATE INDEX idx_run_day ON run (run_date, engine);
A few decisions worth explaining:
model comes from the response, not your config. If you request an alias like gpt-4.1 or sonar, the provider may serve a newer snapshot over time. Store the model string the API sends back so a jump in your chart can be matched to a model change.
UNIQUE (prompt_id, engine, run_date, sample_idx) makes the job idempotent. If the scheduler crashes halfway and restarts, INSERT OR IGNORE skips samples that already exist and you don't pay for them twice.
raw_json costs disk and saves you later. Citation formats change. Engines add fields. Having the full response means you can backfill a new field (for example, the page titles of the sources) without re-querying.
scorer_version makes re-scoring safe. Improve your alias regex, bump v1 to v2, rescore the full history and compare the two versions side by side before switching dashboards over.
The collector
Collection writes only to run. It doesn't detect brands at all.
# collect.py
import json, sqlite3, datetime as dt, time
from engines import ENGINES # functions returning (text, sources, model, raw)
SAMPLES = 5
db = sqlite3.connect("visibility.db")
today = dt.datetime.now(dt.timezone.utc).date().isoformat()
prompts = db.execute("SELECT id, text FROM prompt WHERE active = 1").fetchall()
for prompt_id, text in prompts:
for engine_name, ask in ENGINES.items():
for i in range(SAMPLES):
exists = db.execute(
"SELECT 1 FROM run WHERE prompt_id=? AND engine=? AND run_date=? AND sample_idx=?",
(prompt_id, engine_name, today, i)).fetchone()
if exists:
continue
t0 = time.monotonic()
try:
answer, sources, model, raw = ask(text)
except Exception as e:
print(f"[{engine_name}] prompt {prompt_id} sample {i}: {e}")
continue
db.execute(
"""INSERT OR IGNORE INTO run
(prompt_id, engine, model, sample_idx, run_date,
answer_text, sources, raw_json, latency_ms)
VALUES (?,?,?,?,?,?,?,?,?)""",
(prompt_id, engine_name, model, i, today, answer,
json.dumps(sources), json.dumps(raw),
int((time.monotonic() - t0) * 1000)))
db.commit()
Scoring is a separate script that reads run rows without a score row for the current scorer_version, and fills them in. Keeping it separate means a bug in scoring never costs you API spend.
Scheduling it with GitHub Actions
For a side project or a proof of concept, a scheduled workflow that commits the SQLite file back to the repo works well:
# .github/workflows/collect.yml
name: collect-ai-visibility
on:
schedule:
- cron: "17 6 * * *" # daily, 06:17 UTC; off the hour to avoid the top-of-hour rush
workflow_dispatch:
concurrency: collect # never run two collectors at once
jobs:
collect:
runs-on: ubuntu-latest
timeout-minutes: 60
permissions:
contents: write
steps:
- uses: actions/checkout@v4
- uses: actions/setup-python@v5
with: { python-version: "3.12" }
- run: pip install -r requirements.txt
- run: python collect.py && python score.py
env:
OPENAI_API_KEY: ${{ secrets.OPENAI_API_KEY }}
PERPLEXITY_API_KEY: ${{ secrets.PERPLEXITY_API_KEY }}
- run: |
git config user.name "visibility-bot"
git config user.email "bot@users.noreply.github.com"
git add visibility.db
git commit -m "data: $(date -u +%F)" || echo "no changes"
git push
Two things to know about scheduled workflows: GitHub doesn't guarantee the exact start time (runs can be delayed under load), and scheduled workflows in public repos are disabled after 60 days without repository activity. Your daily data commits count as activity, so the second one is usually fine, but don't be surprised by gaps. That's another reason to group by run_date rather than expecting exactly 24 hours between runs.
Once the database grows past a few tens of MB, move it out of git into object storage or a managed database.
The first query worth running
Daily mention rate per engine for your own brand, with the sample count beside it so nobody reads a 1-of-2 day as a trend:
SELECT r.run_date,
r.engine,
COUNT(*) AS n,
ROUND(AVG(s.mentioned) * 100, 1) AS mention_pct,
ROUND(AVG(s.cited) * 100, 1) AS cited_pct
FROM run r
JOIN score s ON s.run_id = r.id AND s.scorer_version = 'v1'
JOIN brand b ON b.id = s.brand_id AND b.is_ours = 1
GROUP BY r.run_date, r.engine
ORDER BY r.run_date DESC, r.engine;
Build or buy
This schema covers a single brand team. It gets heavier when you add regions (answers differ by country), more engines (Copilot and Google AI Mode don't have the same clean APIs as OpenAI and Perplexity), agencies running dozens of brands, and the UI on top.
That's the infrastructure Vista AI's AI Rank Tracker runs for you: daily tracking across ChatGPT, Gemini, Claude, Perplexity, Copilot and Google AI Mode, prompts grouped by intent stage, a 0–100 visibility index and "quick win" detection. Its prompt research tool also suggests high-intent prompts with estimated volumes, which helps fill the prompt table.
If you build it yourself, the three rules that matter are: immutable prompts, stored raw answers, and versioned scoring.
Top comments (0)