Full graph →

OSRS Flips

OSRS Flips is a market-intelligence pipeline for the Grand Exchange, Old School RuneScape's player-driven marketplace: a Go collector streams price observations into TimescaleDB, and a scheduler filters the market for trading opportunities, has a local LLM rank them, and posts the results to Discord. The project is archived — the game left the rotation — but it earns its place here as a complete, small time-series system whose patterns carried directly into later SFI work.

A game economy is a genuinely good laboratory: thousands of instruments, real supply and demand, a free API, and zero stakes when your analysis is wrong. Every problem below — idempotent ingestion, retention-friendly schemas, config-driven analysis — reappears in industrial telemetry with the stakes turned up.

SFI project: 2025–2026, archived. Snippets are lifted from migrations/002_price_observations.up.sql, pkg/collector/poller.go, and config.yml at current HEAD.

The schema is the contract

Price observations land in a TimescaleDB hypertable — a Postgres table that Timescale transparently partitions into time-based chunks:

CREATE TABLE price_observations (
    item_id        INTEGER NOT NULL,
    observed_at    TIMESTAMPTZ NOT NULL,
    high_price     INTEGER,
    high_time      TIMESTAMPTZ,
    low_price      INTEGER,
    low_time       TIMESTAMPTZ,
    ingested_at    TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (item_id, observed_at)
);

-- Chunks by observed_at, default chunk interval (7 days)
SELECT create_hypertable('price_observations', 'observed_at');

-- Item lookups with time ordering (technical analysis queries)
CREATE INDEX idx_observations_item_time ON price_observations (item_id, observed_at DESC);

Two details do most of the work. The composite primary key (item_id, observed_at) makes ingestion idempotent — re-polling the same minute upserts rather than duplicates, so crash-and-retry needs no dedup logic. And chunking by observed_at means both the hot query pattern ("this item, recent first") and eventual retention ("drop chunks older than N months") align with the physical layout. The general schema reasoning is in storage; running Timescale as an extension of ordinary Postgres is covered in postgresql.

A collector that expects to fail

The collector is a single Go daemon with a deliberately boring loop:

func DefaultPollerConfig() *PollerConfig {
    return &PollerConfig{
        Interval:   60 * time.Second,
        RetryDelay: 10 * time.Second,
        MaxRetries: 5,
        BackoffMax: 5 * time.Minute,
    }
}

func (p *Poller) poll() {
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    observedAt := time.Now().UTC()
    resp, err := p.client.GetLatestPrices(ctx, nil)
    if err != nil {
        p.handleError(err)   // exponential backoff, capped at BackoffMax
        return
    }
    // ... parse ~4,000 items, batch-insert, log counts
}

Everything defensive about it was learned from operating it: the per-poll context timeout (a hung HTTP call must not stall the ticker), UTC stamped once per cycle so every item in a poll shares one observed_at, malformed items skipped with a warning rather than failing the batch, and consecutive-failure counting driving backoff. Schema migrations run on startup via golang-migrate, so a fresh host goes from empty Postgres to ingesting with no manual step. Historical backfill runs alongside live polling in batches, which rebuilt weeks of context after any outage.

Analysis as configuration

The scheduler reads jobs from YAML — each one a filter over the market, a schedule, and an LLM to rank the survivors:

jobs:
  - name: "midrange"
    description: "Viable margins with buy in between 600k and 3.5M"
    enabled: true
    filters:
      margin_pct_min: 3
      insta_sell_price_min: 600000
      insta_sell_price_max: 2500000
      volume_1h_min: 20            # dead items have great margins on paper
      sort_by_after_price: "margin_gp"
    output:
      max_items: 20
    model:
      name: qwen3:8b
      num_ctx: 18000
      temperature: 0.9

schedules:
  - job_name: "midrange"
    cron: "0 0 * * * *"
    enabled: true

The division of labor matters: SQL filters do the quantitative work (margins, price bands, volume floors — the volume_1h_min guard exists because illiquid items always look like the best flips), and only the top-N survivors reach the LLM. The model — a quantized qwen3 running on local ollama hardware — adds qualitative judgment about which opportunities are practical, formatted for Discord. Inference over 20 items is cheap and bounded; inference over the market would be neither.

What carried forward

The register entry is honest that the UX got little polish — the SvelteKit charts and watchlist are desktop-oriented and functional, no more. What proved durable is everything below the UI: poll-parse-batch-insert with idempotent keys became the shape of Symbology's ingestion (see symbology); migrations-on-boot became standard; and config-driven jobs with per-job model settings prefigured Symbology's model_configs.yaml. Archiving a project and keeping its patterns is a good trade.

References