schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-3.0.x"]
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_DATABASE_URL")
}

/// A single observed price — a trade, or a snapshot of a pool's state.
///
/// Identity (#237): a retried ingest batch, or two ingester instances
/// overlapping during a deploy, must not store the same observation twice —
/// duplicated trades inflate volume and skew the volume-weighted VWAP. The
/// unique index below makes a duplicate impossible rather than unlikely.
///
/// eventId is nullable, and Postgres treats NULLs as distinct in a unique
/// index, so the constraint only binds "where eventId exists". That is why
/// upsertPricePoints (src/db.ts) never stores a null: venues that hand back a
/// natural id (SDEX/AMM trade TOIDs, Horizon trade ids) keep it verbatim, and
/// sources with none — AMM/Soroswap pool snapshots, Aquarius, anything added
/// later — get a key synthesized deterministically from the row's content,
/// never from a clock. See synthesizeEventId in src/db.ts and
/// scripts/migrations/2026-10-17-price-point-identity.sql, which removes rows
/// already duplicated on this identity before the index is created.
model PricePoint {
  id            String    @default(uuid())
  network       String    @default("testnet")
  assetA        String    @map("asset_a")
  assetB        String    @map("asset_b")
  pairKey       String    @map("pair_key")
  source        String
  poolId        String?   @map("pool_id")
  price         Decimal   @db.Decimal(36, 18)
  baseVolume    Decimal   @map("base_volume") @db.Decimal(36, 7)
  counterVolume Decimal   @map("counter_volume") @db.Decimal(36, 7)
  ledger        Int
  timestamp     DateTime  // Legacy timestamp column / TimescaleDB hypertable partition key
  observedAt    DateTime  @default(now()) @map("observed_at")
  sourceTime    DateTime? @map("source_time")
  eventId       String?   @map("event_id")

  @@id([id, timestamp])
  @@index([network, pairKey, observedAt(sort: Desc)])
  @@unique([network, source, eventId], map: "price_point_identity")
  @@index([network, pairKey, timestamp(sort: Desc)])
  @@index([network, pairKey, source, observedAt(sort: Desc)])
  @@map("price_points")
}

/// Latest supply/borrow interest rate an asset earns or costs at one lending
/// venue, written by the rate ingester (Blend, see L079) and read verbatim by
/// GET /rates/:asset.
///
/// Every row is scoped to a `network` exactly like price_points: mainnet and
/// testnet rates are separate series and are never pooled. `venue` and `source`
/// name where the number came from, and `poolId`/`poolName` record the specific
/// market it came from so a response can attribute each rate instead of
/// silently averaging a live market with a dead one.
model LendingRate {
  id          String   @default(uuid())
  network     String   @default("testnet")
  asset       String
  assetIssuer String?  @map("asset_issuer")
  /// Lending venue the rate was read from (e.g. "blend").
  venue       String
  /// Distinct source tag stored on the row, mirroring price_points.source.
  source      String
  /// Contract id of the pool/market the rate came from — never elided.
  poolId      String   @map("pool_id")
  poolName    String?  @map("pool_name")
  /// APY earned by supplying the asset, as a fraction (0.05 == 5%).
  supplyRate  Decimal  @map("supply_rate") @db.Decimal(18, 8)
  /// APY charged to borrow the asset, as a fraction (0.07 == 7%).
  borrowRate  Decimal  @map("borrow_rate") @db.Decimal(18, 8)
  ledger      Int
  timestamp   DateTime

  // Reserve state behind the two rates above, written by the Blend ingester.
  // Nullable because a venue that publishes only rates has nothing to put here.
  /// Contract id of the asset (Stellar Asset Contract for classic assets).
  assetAddress  String?  @map("asset_address")
  /// Annual rates before compounding, as fractions; supplyRate/borrowRate are the APYs.
  supplyApr     Decimal? @map("supply_apr") @db.Decimal(18, 8)
  borrowApr     Decimal? @map("borrow_apr") @db.Decimal(18, 8)
  /// Borrowed / supplied, as a fraction (0.5 == 50%).
  utilization   Decimal? @db.Decimal(18, 8)
  totalSupply   Decimal? @map("total_supply") @db.Decimal(36, 7)
  totalBorrowed Decimal? @map("total_borrowed") @db.Decimal(36, 7)
  /// Liquidity left to borrow (totalSupply - totalBorrowed).
  reserves      Decimal? @db.Decimal(36, 7)

  @@id([id, timestamp])
  @@index([network, asset, timestamp(sort: Desc)])
  @@index([network, asset, venue, timestamp(sort: Desc)])
  @@index([network, poolId, asset, timestamp(sort: Desc)])
  @@map("lending_rates")
}

model PoolSnapshot {
  id          String   @default(uuid())
  network     String   @default("testnet")
  poolId      String   @map("pool_id")
  assetA      String   @map("asset_a")
  assetB      String   @map("asset_b")
  reserveA    Decimal  @map("reserve_a") @db.Decimal(36, 7)
  reserveB    Decimal  @map("reserve_b") @db.Decimal(36, 7)
  spotPrice   Decimal  @map("spot_price") @db.Decimal(36, 18)
  totalShares Decimal? @map("total_shares") @db.Decimal(36, 7)
  feeBp       Int      @default(30) @map("fee_bp")
  ledger      Int
  timestamp   DateTime

  @@id([id, timestamp])
  @@index([network, poolId, timestamp(sort: Desc)])
  @@map("pool_snapshots")
}

model PriceAggregate {
  pairKey     String   @map("pair_key")
  network     String   @default("testnet")
  window      String
  bucket      DateTime
  vwap        Decimal  @db.Decimal(36, 18)
  sdexVwap    Decimal? @map("sdex_vwap") @db.Decimal(36, 18)
  ammVwap     Decimal? @map("amm_vwap") @db.Decimal(36, 18)
  volume      Decimal  @default(0) @db.Decimal(36, 7)
  sdexVolume  Decimal? @default(0) @map("sdex_volume") @db.Decimal(36, 7)
  ammVolume   Decimal? @default(0) @map("amm_volume") @db.Decimal(36, 7)
  tradeCount  Int?     @default(0) @map("trade_count")
  openPrice   Decimal? @map("open_price") @db.Decimal(36, 18)
  closePrice  Decimal? @map("close_price") @db.Decimal(36, 18)
  highPrice   Decimal? @map("high_price") @db.Decimal(36, 18)
  lowPrice    Decimal? @map("low_price") @db.Decimal(36, 18)

  @@id([network, pairKey, window, bucket])
  @@map("price_aggregates")
}

model PriceSnapshot {
  pair   String
  network String   @default("testnet")
  ts     DateTime
  price  Decimal  @db.Decimal(36, 18)
  volume Decimal  @default(0) @db.Decimal(36, 7)

  @@id([network, pair, ts])
  @@index([network, pair, ts])
  @@map("price_snapshots")
}

model IndexerState {
  id              String
  network         String   @default("testnet")
  lastCursor      String?   @map("last_cursor")
  lastLedger      Int?      @map("last_ledger")
  lastProcessedAt DateTime? @map("last_processed_at")
  updatedAt       DateTime  @default(now()) @updatedAt @map("updated_at")

  @@id([network, id])
  @@map("indexer_state")
}

model PairConfig {
  pairKey   String   @map("pair_key")
  network   String   @default("testnet")
  assetACode   String   @map("asset_a_code")
  assetAIssuer String?  @map("asset_a_issuer")
  assetBCode   String   @map("asset_b_code")
  assetBIssuer String?  @map("asset_b_issuer")
  addedAt   DateTime @default(now()) @map("added_at")

  @@id([network, pairKey])
  @@map("pair_configs")
}

/// A single x402-discoverable resource in the Bazaar catalog — either an
/// HTTP endpoint or an MCP tool, per the x402 `bazaar` extension
/// (specs/extensions/bazaar.md in x402-foundation/x402).
///
/// HTTP and MCP resources share one table (discriminated by `type`) rather
/// than two, because a discovery listing is fundamentally "a resource with
/// payment requirements and a bazaar.info blob" regardless of transport —
/// splitting them would require the discovery query to UNION two tables on
/// every filter combination for no benefit, since the two types are never
/// looked up via different access patterns.
model BazaarResource {
  id String @id @default(uuid())

  /// "http" | "mcp" — discriminates which of the two input shapes below applies.
  type String

  /// Which Stellar network this listing settles on ("mainnet" | "testnet").
  /// Mirrors config.ts's NetworkName so a listing is never ambiguous about
  /// which network's payTo/asset it refers to.
  network String

  /// The protected resource URL (`resource.url` in the spec). For MCP this is
  /// the MCP server endpoint, not the tool itself — the tool is disambiguated
  /// by `mcpToolName` below.
  url String

  /// `resource.description` — human-readable description of the resource.
  description String?

  /// `resource.mimeType`.
  mimeType String? @map("mime_type")

  /// Optional service metadata the spec allows on `resource`.
  serviceName String?  @map("service_name")
  tags        String[] @default([])
  iconUrl     String?  @map("icon_url")

  /// MCP tool identifier (`input.toolName`). Null for HTTP resources.
  /// Per the spec, MCP resources are keyed on the TUPLE of (resource.url,
  /// input.toolName) since multiple tools multiplex over one server endpoint.
  /// We additionally scope that tuple by `network` (see @@unique below) —
  /// a deliberate deviation, called out in the PR: since Lens is
  /// dual-network, the same (url, toolName) pair can legitimately exist
  /// once per network with a different payTo/asset in `accepts`, and the
  /// spec's tuple alone can't express that without collapsing them.
  mcpToolName String? @map("mcp_tool_name")

  /// HTTP method for HTTP resources (GET/POST/...). Null for MCP resources.
  httpMethod String? @map("http_method")

  /// Full `accepts[]` payment requirements array (scheme/network/amount/asset/
  /// payTo/maxTimeoutSeconds/extra), stored verbatim so the discovery response
  /// can round-trip the exact PaymentRequirements the resource advertised.
  accepts Json

  /// The `payTo` address extracted from accepts[0] for indexed filtering.
  /// Denormalized on write because Postgres cannot efficiently index into a
  /// JSON array element without a functional/GIN index per accepted scheme,
  /// and payTo is the one field the spec calls out as a top-level filter.
  payTo String @map("pay_to")

  /// `extensions.bazaar.info` — discovery metadata (input type, params, output).
  bazaarInfo Json @map("bazaar_info")

  /// `extensions.bazaar.schema` — JSON Schema validating `bazaarInfo`.
  bazaarSchema Json @map("bazaar_schema")

  /// `extensions.bazaar.routeTemplate` — canonical `:param` pattern for
  /// dynamic HTTP routes, used by the facilitator to consolidate listings.
  routeTemplate String? @map("route_template")

  /// Any other declared extension keys beyond "bazaar" (spec's `extensions`
  /// filter matches on presence of a key here, "bazaar" always included).
  extensionKeys String[] @default(["bazaar"]) @map("extension_keys")

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  // HTTP resources are keyed on (network, url, httpMethod); MCP resources are
  // keyed on (network, url, mcpToolName) per the spec's tuple. Postgres
  // treats NULLs as distinct in a unique index, so these two constraints
  // don't collide with each other for a row that only populates one side.
  @@unique([network, url, httpMethod], name: "bazaarHttpIdentity", map: "bazaar_http_identity")
  @@unique([network, url, mcpToolName], name: "bazaarMcpIdentity", map: "bazaar_mcp_identity")
  // Covers the six spec filters (type, payTo, network, extensions via
  // extensionKeys, plus limit/offset) and keeps pagination stable — see
  // routes/discovery.ts, which always orders by (createdAt, id).
  @@index([network, type, payTo, createdAt(sort: Desc), id])
  @@index([extensionKeys], type: Gin)
  @@map("bazaar_resources")
}

/// One settlement attempt through POST /settle.
///
/// Written BEFORE the transaction is submitted, so a resource server that
/// times out and retries finds a record rather than causing a second payment.
/// The idempotency key is the inner transaction hash — derived from the
/// payload, and the same hash the ledger records.
model SettlementAttempt {
  id String @id @default(uuid())

  /// Network the payment settles on, as "testnet" | "mainnet" — the same
  /// vocabulary every other model in this schema uses, so a network filter
  /// works uniformly. The CAIP-2 id ("stellar:testnet") is a wire format and
  /// stays on the wire; storing it here would silently exclude these rows
  /// from any query that filters network the way the rest of the schema does.
  network String @default("testnet")

  /// Inner transaction hash, hex — the idempotency key.
  txHash String @map("tx_hash")

  /// "submitting" | "settled" | "failed". A row stuck in "submitting" is
  /// resolved by reading the ledger, never by resubmitting.
  state String

  payer        String?
  payTo        String? @map("pay_to")
  asset        String?
  amount       String?
  errorReason  String? @map("error_reason")
  errorMessage String? @map("error_message")

  /// The SettleResponse returned to the caller, replayed verbatim on a retry.
  response Json?

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at")

  /// The lock. Two concurrent settles for the same payload race to insert and
  /// exactly one wins; the loser replays the winner's answer.
  @@unique([network, txHash], name: "network_txHash", map: "settlement_attempt_identity")
  @@map("settlement_attempts")
}

model Webhook {
  id        String   @id @default(uuid())
  network   String   @default("testnet")
  url       String
  assetA    String   @map("asset_a")
  assetB    String   @map("asset_b")
  threshold Float
  direction String
  secret    String
  createdAt DateTime @default(now()) @map("created_at")

  @@index([network, assetA, assetB])
  @@map("webhooks")
}

/// Permanent webhook failures are retained for 30-day diagnosis only; replay is
/// not automatic because a delayed alert may no longer describe the current price.
model WebhookDeliveryFailure {
  id             String   @id @default(uuid())
  endpoint       String
  payload        Json
  attemptCount   Int      @map("attempt_count")
  lastStatus     Int?     @map("last_status")
  errorCategory  String   @map("error_category")
  firstAttemptAt DateTime @map("first_attempt_at")
  lastAttemptAt  DateTime @map("last_attempt_at")
  createdAt      DateTime @default(now()) @map("created_at")

  @@index([createdAt(sort: Desc)])
  @@map("webhook_delivery_failures")
}

model ApiKey {
  id               String    @id @default(uuid())
  hash             String    @unique
  label            String
  ratePerMin       Int       @default(60) @map("rate_per_min")
  ratePerDay       Int       @default(10000) @map("rate_per_day")
  revokedAt        DateTime? @map("revoked_at")
  expiresAt        DateTime? @map("expires_at")
  createdAt        DateTime  @default(now()) @map("created_at")
  lastUsedAt       DateTime? @map("last_used_at")
  monthlyQuotaCents Int?     @default(10000) @map("monthly_quota_cents")
  dailyQuotaCents  Int?      @default(500) @map("daily_quota_cents")
  overagePolicy    String    @default("block") @map("overage_policy")
  scopes           String[]  @default([])

  @@map("api_keys")
}

model VenueDispersion {
  id               String   @id @default(uuid())
  network          String   @default("testnet")
  pairKey          String   @map("pair_key")
  timestamp        DateTime @default(now())
  venueCount       Int      @map("venue_count")
  median           Decimal? @db.Decimal(36, 18)
  dispersionPct    Decimal? @map("dispersion_pct") @db.Decimal(10, 4)
  spreadBps        Decimal? @map("spread_bps") @db.Decimal(10, 2)
  minPrice         Decimal? @map("min_price") @db.Decimal(36, 18)
  maxPrice         Decimal? @map("max_price") @db.Decimal(36, 18)
  outlierVenue     String?  @map("outlier_venue")
  outlierDeviation Decimal? @map("outlier_deviation") @db.Decimal(10, 4)
  status           String   @default("ok")
  isAmbiguous      Boolean  @default(false) @map("is_ambiguous")
  freshVenues      String[] @default([]) @map("fresh_venues")
  staleVenues      String[] @default([]) @map("stale_venues")
  venues           Json?

  @@index([network, pairKey, timestamp(sort: Desc)])
  @@map("venue_dispersions")
}

/// One value Lens put on chain through its SEP-40 feed (#222).
///
/// Rows exist for WRITES, not for decisions: a blocked tick is a log line, and
/// a table that recorded every "below_deviation" would hold ~2,880 rows per
/// pair per day and say nothing an operator could not read from the trigger
/// code in the logs. What this table proves is the opposite direction — every
/// number a contract was told, with the venues that produced it, which is the
/// evidence that "a price you cannot source is worse than no price" was
/// actually enforced.
model OraclePublication {
  id      String @id @default(uuid())
  network String @default("testnet")

  /// The Lens pair this came from. Kept alongside the feed asset because one
  /// pair can only ever feed one quoted asset, but the reverse is not true:
  /// without pairKey you cannot trace a published number back to a trade.
  pairKey String @map("pair_key")

  /// SEP-40 identifier of the asset whose price in `base` this is: a C...
  /// Stellar Asset Contract id, or the code for native XLM. Never a bare code
  /// for an anchored asset — a code is not an issuer, and two tokens share one
  /// code in the wild.
  quotedAsset String @map("quoted_asset")
  baseAsset   String @map("base_asset")

  /// "published" | "failed". "published" is written only after the transaction
  /// finalized, so a row here is a claim about the ledger, not about intent.
  state String

  /// Why the write happened: "first_report" | "deviation" | "max_interval".
  trigger String?

  /// Human-readable price, the same number /api/price would return for the
  /// pair at that moment, oriented against the feed's base.
  price Decimal? @db.Decimal(36, 18)

  /// The exact i128 written on chain, as text. Prisma's Decimal cannot hold an
  /// i128 range, and re-deriving it from `price` would defeat the point of
  /// keeping it.
  scaledPrice String? @map("scaled_price")
  decimals    Int?
  /// Feed tick the value was written at (`floor(unix_now()/resolution)*resolution`).
  feedTimestamp BigInt? @map("feed_timestamp")
  resolution    Int?

  /// Every venue behind the number, joined with ','. This is the provenance
  /// guarantee: a row without venues should not exist, and `sourceCount` is
  /// `venues.length` because the contract derives it from the same list.
  venues      String?
  sourceCount Int?    @map("source_count")

  /// Percent moved against the value that was on chain, when there was one.
  deviationPct Float? @map("deviation_pct")

  /// The publisher's own sentence for the decision — the same string that went
  /// to the log, so a log grep and a table query agree.
  reason String?

  txHash       String? @map("tx_hash")
  errorMessage String? @map("error_message")

  createdAt DateTime @default(now()) @map("created_at")

  @@index([network, pairKey, createdAt(sort: Desc)])
  @@index([network, quotedAsset, feedTimestamp(sort: Desc)])
  @@map("oracle_publications")
}