Torn-Vault-Tracker/docs/superpowers/specs/2026-08-03-torn-api-v2-migration-design.md

Torn API v1 → v2 Migration Design

Date: 2026-08-03 Project: Torn Vault Tracker Status: Approved — pending implementation

Problem

The Torn City API has moved from v1 (api.torn.com/user/) to v2 (api.torn.com/v2/user). The two protocols return different JSON shapes and use different authentication:

Concern	v1 (old)	v2 (new)
Endpoint	https://api.torn.com/user/?selections=log&…&key=APIKEY	https://api.torn.com/v2/user?selections=log&… + Authorization: ApiKey KEY
log shape	Object keyed by entry id	Flat array of entry objects
Entry id	$entry['log'] (numeric event type — duplicated across entries)	$entry['id'] (string, unique per entry)
title location	$entry['title'] (root)	$entry['details']['title']
timestamp location	$entry['timestamp'] (root)	$entry['timestamp'] (root, unchanged)
Pagination	&to=$ts, &from=$ts	_metadata.links.next URL with from/to/sort/limit params

The project was already partially migrated (firstRun() in functions.php:72 calls the v2 endpoint) but still reads the v1 JSON shape and v1 authentication, so firstRun() does not work. getLog() still uses the v1 endpoint entirely. Both code paths need to be brought onto v2 — a single coherent rewrite — and the helpers in includes/utilities.php (which both rely on) need to match.

Sample files live at backup/api-sample.json (v1, 2 entries) and backup/api-sample-new.json (v2, 100 entries with _metadata).

Goals

• A single v2 path that handles both bootstrap (full history) and steady-state (recent sync).
• Idempotent re-runs: re-fetching the same data does not produce duplicate rows.
• Existing users with a populated vault.db come through the migration without manual intervention — only an empty DB triggers the full bootstrap.
• New code is covered by a unit-style golden-file test that doesn't touch the network.

Non-Goals

• Adding new API endpoints beyond user/selections=log&log=5850,5851.
• Changing the local DB schema (table shape is already v2-compatible thanks to id TEXT PRIMARY KEY).
• UI changes — index.php, style.css, script.js stay identical.
• README updates — content already describes current behavior accurately.

Architecture

index.php
├── config.php
├── functions.php        (high-level: backfillUserLogs, syncUserLogs, DB, UI helpers)
└── includes/
    ├── utilities.php    (low-level: API + DB helpers; all v2-aware)
    └── exceptions.php   (unchanged)

The two-phase flow on each request:

1. Bootstrap (empty DB) → backfillUserLogs($user) paginates full history.
2. Steady state (DB populated) → syncUserLogs($user) fetches only newer entries.

Both call one shared fetchAndStoreLogPage() helper that performs a single page pull, stores rows idempotently, and returns either the next _metadata.links.next URL or null.

Components and Responsibilities

includes/exceptions.php — unchanged

Existing ApiKeyMissingException, CurlErrorException, JsonDataException, ApiValidationException cover all the failure modes we anticipate. Add one optional exception:

• LogEntryIncompleteException — raised when a fetched entry is missing data.deposited or data.withdrawn (defensive — shouldn't occur given we filter to log=5850,5851, but we shouldn't silently insert garbage if the API ever changes).

includes/utilities.php — rewritten

Function	Change
executeApiCall(string $url, string $apiKey): array	Rewrite. Drop Content-Type: application/json; add Authorization: ApiKey <apiKey>. Accepts a complete URL (so callers can pass through _metadata.links.next). If $apiKey is empty, throws ApiKeyMissingException. The apiKey is supplied by the caller (fetchAndStoreLogPage looks it up from USER_KEYS[$user]); this keeps executeApiCall agnostic about which user is being fetched.
validateApiResponse(array)	Tighten: log must exist and be an array (not an object-keyed map).
processLogEntries(array $logEntries, string $user, …)	Rewrite for v2 shape. Read $entry['id'] for the PK, $entry['timestamp'], $entry['details']['title'], $entry['data']['deposited'] (deposit) or $entry['data']['withdrawn'] (withdraw). Use INSERT … ON CONFLICT(id) DO NOTHING so the operation is idempotent. Skip (and log) any entry whose data block lacks both fields rather than inserting bad data.
fetchAndStoreLogPage(PDO $pdo, string $user, string $url): ?string	New. Composes executeApiCall + validateApiResponse + processLogEntries for a single page. Looks the API key up via USER_KEYS[$user]. Returns $data['_metadata']['links']['next'] ?? null.
dbNew(), ensureUserHasApiKey(), fetchVaultRecords(), vaultLoop(), getSign(), consoleLog()	Unchanged.

functions.php — rewritten entry points

Function	Change
getDatabaseConnection()	Unchanged.
backfillUserLogs(string $user): void	New (replaces firstRun). Starts at INITIAL_URL = "https://api.torn.com/v2/user?selections=log&log=5850,5851", paginates with _metadata.links.next until exhausted.
syncUserLogs(string $user): void	Renamed from getLog. Uses the v2 endpoint with &from=<last_seen_ts+1> query param (only if the user has rows in the DB — otherwise delegates to backfillUserLogs). Falls back to following _metadata.links.next if more than one page comes back.
generateBalance(), getSpace(), buildTable()	Unchanged — they read from the DB, schema is unchanged.

index.php — one-line swap

Replace firstRun → backfillUserLogs and getLog → syncUserLogs. Logic for "is DB empty?" already lives in dbNew(), so the control flow is identical.

config.php — unchanged

USER_KEYS map keyed by user already provides both API keys; the migration uses them both.

Data Flow

Bootstrap (empty DB)

backfillUserLogs(user) {
    url = "https://api.torn.com/v2/user?selections=log&log=5850,5851"
    do {
        next = fetchAndStoreLogPage($pdo, $user, $url)
        url  = next
    } while (next !== null)
}

fetchAndStoreLogPage(pdo, user, url): ?string {
    headers = ["Authorization: ApiKey " . USER_KEYS[user]]
    data    = executeApiCall(url, USER_KEYS[user])   // throws on curl/JSON failure
    validateApiResponse(data)                     // throws on shape error

    processLogEntries(data['log'], user, …)       // INSERT ... ON CONFLICT DO NOTHING
    return data['_metadata']['links']['next'] ?? null
}

Steady state (DB populated)

syncUserLogs(user) {
    if (dbNew()) return backfillUserLogs(user)         // safety net

    last = $pdo->query("SELECT MAX(timestamp) FROM vault WHERE user = ?")
    from = (int)last + 1
    url  = "https://api.torn.com/v2/user?selections=log&log=5850,5851&from=$from"

    do {
        next = fetchAndStoreLogPage($pdo, $user, $url)
        url  = next
    } while (next !== null)
}

Data Model

The vault table (defined in functions.php:32-38) already matches our needs:

CREATE TABLE IF NOT EXISTS vault (
    id          TEXT PRIMARY KEY,    -- v2 entry id, e.g. "j8EzaeOu2lpWPcloqGYJ"
    user        TEXT NOT NULL,
    timestamp   INTEGER NOT NULL,
    description TEXT NOT NULL,
    amount      REAL NOT NULL
);

Migration concern: existing rows were inserted with $key from the v1 object-keyed log, which means the current PK is the v1 alphanumeric object key (e.g. "CTczoijHAKhnfAeYmagC"). The v2 PK is the v2 unique id (e.g. "j8EzaeOu2lpWPcloqGYJ"). Format-wise both are strings, so the schema is compatible — but the values do not match and v2 inserts will collide on the PK only if the same id repeats, which it won't across the two schemes. Wiping data/vault.db on the first page load after deploy gives the cleanest result (recommended in install note). For users with an existing DB, the bootstrap path will simply keep fetching — v2 ids that already exist (by coincidence — vanishingly unlikely between v1 object keys and v2 entry ids) will be skipped via ON CONFLICT(id) DO NOTHING.

Error Handling

Failure	Behavior
Missing API key for a user	ensureUserHasApiKey() throws ApiKeyMissingException before any fetch.
curl_exec returns false	executeApiCall throws CurlErrorException. The bootstrap loop stops; index page renders whatever was stored so far.
json_decode fails	executeApiCall throws JsonDataException. Same as above.
Response missing log or wrong shape	validateApiResponse throws ApiValidationException. Loop stops.
Entry lacks data.deposited or data.withdrawn	processLogEntries raises LogEntryIncompleteException for that entry, skips it, continues with the rest. (Logged via the existing consoleLog and debug.log patterns.)
DB error	Handled by PDO exception mode; propagates.

All exceptions are caught at the top of index.php's bootstrap path so a single bad page doesn't take down the whole UI. The user sees whatever data is in the DB plus a non-fatal warning.

Testing Strategy

A pure-PHP "golden file" test that does not hit the network:

1. Load backup/api-sample-new.json.
2. Mock executeApiCall to return the fixture ([api-sample-new.json content]).
3. Mock processLogEntries's PDO statements (count execute() calls and capture params).
4. Assert: every entry produces exactly one INSERT with the expected id, user, timestamp, description, amount; that the loop terminates when links.next is null; and that a fixture with links.next set to a sentinel URL causes exactly one extra fetch.

This lives next to phpcs.xml (no test framework added; we just add tests/process_log_entries_test.php and document how to run it via php tests/process_log_entries_test.php). The test is run manually before each deploy.

We do not add live API smoke tests — they require real keys and quota. The existing dbNew() path on index.php is the only "live" verification, and it runs only on the user's first page-load.

Files Touched

File	Action
functions.php	Rewrite the two log-fetch entry points; keep DB + UI helpers.
includes/utilities.php	Rewrite executeApiCall, validateApiResponse, processLogEntries; add fetchAndStoreLogPage.
includes/exceptions.php	Add LogEntryIncompleteException.
index.php	Rename calls: firstRun→backfillUserLogs, getLog→syncUserLogs.
tests/process_log_entries_test.php	New. Golden-file unit test for v2 parsing.
README.md	Optional: note that data/vault.db will be re-populated on first page-load post-deploy.
config.php, style.css, script.js, backup/	Untouched.

Open Questions

None at design time. All clarifications resolved during brainstorming.

Acceptance Criteria

1. getLog/syncUserLogs and firstRun/backfillUserLogs both call https://api.torn.com/v2/user with Authorization: ApiKey … header.
2. processLogEntries reads id, timestamp, details.title, and data.deposited/data.withdrawn from each entry; ignores any other shape.
3. Pagination follows _metadata.links.next until null.
4. Re-running on the same data produces no duplicate rows.
5. tests/process_log_entries_test.php passes against backup/api-sample-new.json.
6. index.php, style.css, script.js, includes/exceptions.php (existing classes), config.php behave identically to before for end users — except that the data they fetch is now the v2 shape.