Log model, prompt, and version per request for AI answer traceability
For developers shipping LLM-backed features who need to explain later why a specific answer was produced. This shows a concrete request-logging pattern with PostgreSQL, Node.js, and HTTP headers so every response can be tied back to the exact model, prompt template version, and request payload hash.
TL;DR — Store one immutable row per model request with: request ID, model name, provider/model version string, prompt template version, input hash, generation parameters, response ID, and timestamps. The single most useful fix is to stop relying on app logs alone: write these fields to a database table in the request path before returning the answer, and return the request ID to callers in an
X-Request-Idheader. Reading time: ~5 min
Goal
When you finish, every AI response your app returns will be traceable to a database row containing the exact model identifier, prompt template version, generation settings, request/response IDs, and a reproducible hash of the input so you can explain later what produced the answer without searching scattered logs.
Prerequisites
- PostgreSQL 14+ reachable from your app — check with
psql --version - Node.js 20+ — check with
node --version - A server app where you call an LLM provider over HTTP
- Permission to run a database migration in the target environment
- A stable prompt template versioning scheme, for example
support_reply_v12 - Your app’s environment variable mechanism (
.env, systemd, container env, or CI secrets) - A way to identify the model string returned or configured for each request, for example
gpt-4.1-miniorclaude-sonnet-4-5
Steps
Step 1: Create a table for immutable per-request records
⚠️ This migration adds a table and indexes. It should not cause downtime on a normal PostgreSQL deployment, but run it through your normal migration process if schema changes are controlled.
CREATE TABLE IF NOT EXISTS ai_request_log (
id BIGSERIAL PRIMARY KEY,
request_id UUID NOT NULL UNIQUE,
app_user_id TEXT NULL,
route TEXT NOT NULL,
provider TEXT NOT NULL,
model TEXT NOT NULL,
model_version TEXT NULL,
prompt_template TEXT NOT NULL,
prompt_version TEXT NOT NULL,
input_sha256 CHAR(64) NOT NULL,
input_preview TEXT NULL,
temperature NUMERIC(4,3) NULL,
top_p NUMERIC(4,3) NULL,
max_tokens INTEGER NULL,
provider_request_id TEXT NULL,
provider_response_id TEXT NULL,
http_status INTEGER NULL,
latency_ms INTEGER NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_request_log_created_at ON ai_request_log (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ai_request_log_model_prompt_version ON ai_request_log (model, prompt_version);
CREATE INDEX IF NOT EXISTS idx_ai_request_log_app_user_id ON ai_request_log (app_user_id);
What you should see when this succeeds: CREATE TABLE and three CREATE INDEX lines from psql.
Step 2: Add exact environment variables for prompt versioning
Put these in your app environment.
export AI_PROVIDER=openai
export AI_MODEL=gpt-4.1-mini
export AI_MODEL_VERSION=gpt-4.1-mini
export AI_PROMPT_TEMPLATE=support_reply
export AI_PROMPT_VERSION=v12
What you should see when this succeeds: env | grep '^AI_' prints all five variables.
Step 3: Install the exact packages needed in Node.js
npm install pg uuid
What you should see when this succeeds: npm exits with code 0 and prints added package counts.
Step 4: Add request logging code around the model call
Create src/ai-log.js.
import crypto from "node:crypto";
import { randomUUID } from "node:crypto";
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
export function sha256Json(value) {
return crypto.createHash("sha256").update(JSON.stringify(value)).digest("hex");
}
export function buildAiMeta(input) {
return {
requestId: randomUUID(),
provider: process.env.AI_PROVIDER,
model: process.env.AI_MODEL,
modelVersion: process.env.AI_MODEL_VERSION || process.env.AI_MODEL,
promptTemplate: process.env.AI_PROMPT_TEMPLATE,
promptVersion: process.env.AI_PROMPT_VERSION,
inputSha256: sha256Json(input),
inputPreview: JSON.stringify(input).slice(0, 500),
};
}
export async function insertAiRequestLog(meta) {
await pool.query(
`INSERT INTO ai_request_log (
request_id, app_user_id, route, provider, model, model_version,
prompt_template, prompt_version, input_sha256, input_preview,
temperature, top_p, max_tokens
) VALUES (
$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13
)`,
[
meta.requestId,
meta.appUserId ?? null,
meta.route,
meta.provider,
meta.model,
meta.modelVersion,
meta.promptTemplate,
meta.promptVersion,
meta.inputSha256,
meta.inputPreview,
meta.temperature ?? null,
meta.topP ?? null,
meta.maxTokens ?? null,
]
);
}
export async function finalizeAiRequestLog(meta) {
await pool.query(
`UPDATE ai_request_log
SET provider_request_id = $2,
provider_response_id = $3,
http_status = $4,
latency_ms = $5
WHERE request_id = $1`,
[
meta.requestId,
meta.providerRequestId ?? null,
meta.providerResponseId ?? null,
meta.httpStatus ?? null,
meta.latencyMs ?? null,
]
);
}
What you should see when this succeeds: your app starts without Error: Cannot find module 'pg' or syntax errors.
Step 5: Wrap one real endpoint and return the request ID to the caller
Example Express route in src/server.js.
import express from "express";
import { buildAiMeta, insertAiRequestLog, finalizeAiRequestLog } from "./ai-log.js";
const app = express();
app.use(express.json());
app.post("/api/answer", async (req, res) => {
const started = Date.now();
const input = { question: req.body.question, context: req.body.context ?? "" };
const meta = buildAiMeta(input);
meta.appUserId = req.header("X-User-Id") || null;
meta.route = "/api/answer";
meta.temperature = 0.2;
meta.topP = 1.0;
meta.maxTokens = 500;
await insertAiRequestLog(meta);
let providerResponse;
try {
providerResponse = await fetch(process.env.LLM_API_URL, {
method: "POST",
headers: {
"Content-Type": "application/json",
"Authorization": `Bearer ${process.env.LLM_API_KEY}`,
"X-Request-Id": meta.requestId
},
body: JSON.stringify({
model: meta.model,
input,
temperature: meta.temperature,
top_p: meta.topP,
max_tokens: meta.maxTokens,
metadata: {
prompt_template: meta.promptTemplate,
prompt_version: meta.promptVersion,
request_id: meta.requestId
}
})
});
const body = await providerResponse.json();
await finalizeAiRequestLog({
requestId: meta.requestId,
providerRequestId: providerResponse.headers.get("x-request-id"),
providerResponseId: body.id || null,
httpStatus: providerResponse.status,
latencyMs: Date.now() - started
});
res.setHeader("X-Request-Id", meta.requestId);
res.status(200).json({ request_id: meta.requestId, answer: body.output_text || body.answer || body.content });
} catch (err) {
await finalizeAiRequestLog({
requestId: meta.requestId,
httpStatus: 500,
latencyMs: Date.now() - started
});
res.setHeader("X-Request-Id", meta.requestId);
res.status(500).json({ request_id: meta.requestId, error: "ai_request_failed" });
}
});
app.listen(3000, () => console.log("listening on :3000"));
What you should see when this succeeds: listening on :3000 and your endpoint returns JSON with request_id.
Step 6: Start the app and send one test request
node src/server.js
In another terminal:
curl -i -X POST http://localhost:3000/api/answer \
-H 'Content-Type: application/json' \
-H 'X-User-Id: user-123' \
--data '{"question":"Can I reset my MFA device?","context":"User is locked out of authenticator app"}'
Expected response shape:
HTTP/1.1 200 OK
X-Request-Id: 7d7e6d9e-6b54-4d8d-a2a8-f0d6d7d2f4f8
Content-Type: application/json; charset=utf-8
{"request_id":"7d7e6d9e-6b54-4d8d-a2a8-f0d6d7d2f4f8","answer":"..."}
What you should see when this succeeds: HTTP 200, an X-Request-Id header, and a JSON body containing the same request_id.
Verify it works
Query PostgreSQL for the request you just made.
SELECT request_id, provider, model, model_version, prompt_template, prompt_version, input_sha256, http_status, latency_ms, created_at
FROM ai_request_log
ORDER BY created_at DESC
LIMIT 3;
Expected output shape:
request_id | provider | model | model_version | prompt_template | prompt_version | input_sha256 | http_status | latency_ms | created_at
--------------------------------------+----------+--------------+---------------+-----------------+----------------+-------------------------------------------------------------------+-------------+------------+-------------------------------
7d7e6d9e-6b54-4d8d-a2a8-f0d6d7d2f4f8 | openai | gpt-4.1-mini | gpt-4.1-mini | support_reply | v12 | 8d7f4b6f8a1f7d6c8f8d2d2b8f0a9f7e0f5d7b4e2b1c3d4e5f6a7b8c9d0e1f2a | 200 | 842 | 2026-10-01 12:34:56.123456+00
Also verify the request ID round-trip from HTTP to DB.
REQ_ID=$(curl -s -D - -X POST http://localhost:3000/api/answer -H 'Content-Type: application/json' --data '{"question":"test"}' | awk '/^X-Request-Id:/ {print $2}' | tr -d '\r') && echo "$REQ_ID"
psql "$DATABASE_URL" -c "SELECT request_id, model, prompt_version FROM ai_request_log WHERE request_id = '$REQ_ID';"
Expected result: one row with the same UUID, your configured model, and your configured prompt version.
Common pitfalls
Prompt text stored raw with PII
Mistake: writing the full user input or full rendered prompt into the database.
Symptom: privacy review flags the table, or production data contains secrets and customer content you did not intend to retain.
One-line fix: store input_sha256 plus a short input_preview, and keep the full prompt only in a redacted audit store if policy allows it.
No immutable prompt version
Mistake: using support_reply without a version like v12.
Symptom: two answers claim to use the same prompt, but the template changed in Git and you cannot reconstruct which text was active.
One-line fix: set AI_PROMPT_VERSION from your deploy artifact or commit-tagged template version and write that exact string per request.
Depending only on provider logs
Mistake: assuming the provider’s dashboard or request IDs are enough.
Symptom: you can find a provider request ID but not which app user, route, or prompt version triggered it.
One-line fix: generate your own UUID in the app, send it upstream in X-Request-Id, return it to the client, and store it locally first.
Updating the same row after streaming starts without an initial insert
Mistake: inserting the log row only after the model response finishes.
Symptom: failed or timed-out requests have no record at all, which is exactly when you need traceability.
One-line fix: INSERT before the provider call, then UPDATE status and latency in a finally path.
Model alias changed under you
Mistake: logging only a friendly alias like latest or sonnet.
Symptom: answer quality changes between days, but the database shows the same model name for both requests.
One-line fix: store both the configured alias in model and the provider-returned concrete version string in model_version when available.
Missing request ID in client-visible responses
Mistake: storing the row but not returning the ID to the caller.
Symptom: support tickets include screenshots of bad answers with no way to locate the exact request.
One-line fix: set X-Request-Id and include request_id in the JSON response body on both success and error paths.
This article was written by an AI system and published pending human review. Verify anything you intend to act on.
Have a project in mind?
Get an instant AI price estimate for it, or talk directly to our team.
One email a month on what we learn building with AI