04 — Data Architecture

Ownership rule

Every table has exactly one owning service. Others read it through that service's API, never by connecting to its database. Cross-service joins are a design smell here, not a shortcut.

Gateway schema (hunter-gateway)

apikey
  id            PK
  key_hash      TEXT  UNIQUE INDEX   -- SHA-256 of the plaintext, never the key
  prefix        TEXT                 -- "hk_live_ab12", shown in the console
  name          TEXT
  wallet        TEXT  INDEX          -- owner, lowercase 0x…
  created_at    TIMESTAMPTZ
  revoked_at    TIMESTAMPTZ NULL     -- soft revoke, preserves ledger joins
  retain_prompts BOOL DEFAULT FALSE  -- per-key privacy override

usagerecord                          -- the billing source of truth
  id                PK
  api_key_id        INT INDEX        -- 0 for x402 (no key involved)
  wallet            TEXT INDEX       -- API-key owner or x402 payer
  model             TEXT             -- public model id
  prompt_tokens     INT
  completion_tokens INT
  credits           FLOAT            -- (tokens/1000) × model multiplier
  created_at        TIMESTAMPTZ INDEX

Note what is absent: no prompt column, no completion column. Only counts. That is a schema-level privacy guarantee — there is nowhere for prompt content to be stored even by accident (§08).

Quota is computed as SUM(credits) WHERE wallet = ? AND created_at >= month_start against the tier's monthly allowance. Keeping the ledger append-only means billing disputes are answerable from raw rows.

Platform schema (hunter-platform)

signals
  id             PK
  external_id    TEXT UNIQUE     -- idempotency key from the publisher
  posted_at      TIMESTAMPTZ     -- when the call went public
  asset          TEXT            -- upper-cased symbol
  direction      TEXT            -- bullish | bearish | neutral  (CHECK)
  thesis         TEXT
  source_url     TEXT NULL

signal_outcomes
  id                PK
  signal_id         INT UNIQUE FK → signals(id) ON DELETE CASCADE
  horizon_hours     INT
  price_change_pct  FLOAT NULL
  verdict           TEXT          -- hit | miss | inconclusive  (CHECK)
  notes             TEXT NULL
  scored_at         TIMESTAMPTZ

Design points: - external_id UNIQUE + ON CONFLICT DO NOTHING makes publication idempotent — a retried worker cannot inflate the record. - One outcome per signal (signal_id UNIQUE, upsert on conflict): a call can be re-scored but not double-counted. - Accuracy counts only hit/miss; inconclusive and unresolved signals are reported but excluded from the ratio, so the number cannot be gamed by publishing vague calls.

[PLANNED] entitlements(wallet PK, tier, source, expires_at, updated_at) written by the indexer, read by the gateway.

Agent schema (hunter-agent)

room(id, name UNIQUE)            -- one conversation context
chathistory(id, room_id FK, role, content, timestamp)
usagestats(id, room_id FK, total_tokens)
document(id, hash UNIQUE, text, embedding VECTOR)   -- pgvector knowledge base

document.hash deduplicates on normalised text, so re-ingesting a guide is a no-op. Retrieval is exposed to the agent as the search_knowledge tool rather than stuffed into the prompt — the model decides when it needs the corpus.

Data classification and retention

Consistency model

• Entitlement is eventually consistent. Chain → indexer → platform DB → gateway cache (~300 s). Worst case a user waits a few minutes after locking. Acceptable; the alternative is an RPC call on every inference request.
• Usage is strongly consistent within the gateway: the record is committed before the response is returned, and quota checks read committed rows.
• The chain is never overwritten by off-chain state. If the cache and the chain disagree, the chain wins and the cache is wrong — always refresh from tierOf, never "fix" the chain to match the database.