Skip to content
 
 

Repository files navigation

OurDAO logo

OurDAO Backend

CI License: MIT

Off-chain indexer + read API for the OurDAO lending DAO on Stellar/Soroban.

The Soroban contract (ourdao-contracts) is the single source of truth for all state, but on-chain data has state expiration (TTL) and keeps no queryable history — there's no way to ask the contract "list every loan proposal" or "show me this address's notification feed." This service fills that gap: it tails the contract's emitted events into Postgres and serves fast, aggregated, history-aware read APIs that ourdao-frontend consumes.

It is strictly read-only and event-driven — it never holds keys, never signs a transaction, and cannot move funds. Every state change still happens on-chain via the user's own wallet; this service only mirrors what already happened.

This repository is one of three that make up OurDAO:

Repo Role
ourdao-contracts The Soroban contract — the single source of truth for all DAO state
ourdao-backend (this repo) Off-chain indexer + read API
ourdao-frontend Next.js web app members actually use

Table of contents

Architecture

Soroban RPC ──getEvents──▶ indexer (worker.ts) ──▶ Postgres ──▶ REST API (index.ts) ──▶ frontend
  • src/indexer — a poll loop over the Soroban RPC getEvents, resuming from a persisted cursor (indexer_cursor table) rather than re-scanning from genesis on every restart. Each raw event is written to an append-only events log, then folded into the relevant derived table (members, loan_proposals, loans, treasury_proposals, notifications) inside a single database transaction, so a crash mid-poll can never leave the derived tables and the raw log inconsistent. Poll failures back off exponentially (capped, configurable) instead of hammering the RPC endpoint.
  • src/stellar/events.ts — the event catalog: the exact topic-symbol → data-tuple mapping the contract publishes, decoded via scValToNative and converted to JSON-safe primitives (bigints become strings, since JSON has no native 128-bit integer type).
  • src/api — a Fastify server exposing the read endpoints in the API reference below.
  • src/db — the Postgres schema (applied idempotently on boot by both the API and worker processes) and a thin query helper over pg.

The API process and the indexer worker are separate entrypoints (index.ts / worker.ts) so they can be scaled or deployed independently — e.g. one long-running indexer worker behind several stateless, horizontally-scaled API instances.

Quick start

# 1. Install
npm install

# 2. Start Postgres (or point DATABASE_URL at your own instance)
docker compose up -d

# 3. Configure
cp .env.example .env
#   set CONTRACT_ID to your deployed OurDAO contract id (starts with C)

# 4. Run the API (http://localhost:4000)
npm run dev

# 5. In another terminal, run the indexer
npm run dev:worker

Production build:

npm run build
npm start              # API
npm run start:worker   # indexer

Configuration

All configuration is environment-driven — see .env.example for the full annotated list. Key values:

Variable Purpose
CONTRACT_ID Deployed OurDAO contract id. Required for the indexer to run.
SOROBAN_RPC_URL Soroban RPC endpoint (defaults to public testnet).
NETWORK_PASSPHRASE Testnet by default; switch for mainnet.
DATABASE_URL Postgres connection string (or set the individual PG* vars).
START_LEDGER / START_LOOKBACK_LEDGERS Where to start indexing on a cold start. Public Soroban RPC only retains ~24h of events, so an old start ledger gets clamped to the oldest the RPC still serves.
POLL_INTERVAL_MS / EVENTS_PAGE_LIMIT Indexer poll cadence and page size.
POLL_MAX_BACKOFF_MS Cap for exponential backoff after consecutive poll failures (default 60s).
DRAIN_MAX_PAGES Max pages per poll drain cycle when catching up (default 20).
DRAIN_MAX_MS Max wall-clock ms for a single drain cycle (default 30s).
INDEXER_STALE_AFTER_MS How long (ms) the cursor can be idle before /ready reports stale (default 120s).
INDEXER_QUARANTINE_AFTER_FAILURES After this many consecutive whole-page failures with the same error on the same page, the poller quarantines the offending event(s) instead of retrying forever (default 3). See Quarantine.
INDEXER_RESET_ON_CONTRACT_CHANGE true for one boot to wipe the cursor + derived tables when CONTRACT_ID changes (redeploy). Default false — a mismatch refuses to start. See Redeploying the contract.
CORS_ORIGIN Comma-separated allowed origins for the API (the frontend's URL). Defaults to http://localhost:3000. Set to * to allow all origins (a warning is logged at startup).
RATE_LIMIT_MAX Global rate limit: max requests per window per IP (default 100).
RATE_LIMIT_WINDOW_MS Rate limit window in milliseconds (default 60000).
RATE_LIMIT_EVENTS_MAX Stricter rate limit for GET /api/events (default 30).
STATS_CACHE_MS How long (ms) an /api/stats result is cached in-process before it is recomputed (default 5000; 0 disables). Reported figures are at most this stale.
TRUST_PROXY Set to "true" behind a reverse proxy so rate limits apply per client IP.
TEST_DATABASE_URL Separate database npm test runs against — never the dev DB.

Database schema

Postgres, applied by src/db/migrate.ts on every boot — both the API and the worker call it at startup, so it's safe with no separate migration-runner step to remember to run.

src/db/schema.sql is the bootstrap baseline: idempotent CREATE TABLE/INDEX IF NOT EXISTS statements describing the current desired shape. That's sufficient for a brand-new database, but IF NOT EXISTS silently no-ops on a table that already exists — including when a column was added or a type changed. Those changes instead live as numbered files in src/db/migrations/ (e.g. 0001_widen_vote_columns.sql), applied in order and tracked in a schema_migrations table so each one runs exactly once per database. A fresh database created from schema.sql already has every migration's end state, so its migrations are recorded as applied without re-running their SQL; an existing database gets the real ALTER statements. A Postgres advisory lock serializes migrate() across the API and worker so they don't race to apply the same migration concurrently on startup.

To add a schema change: update schema.sql to the new desired shape (for fresh databases) and add a new numbered file under src/db/migrations/ with the ALTER/CREATE/etc. needed to get an existing database there (for everyone else).

Table Purpose Notable columns
schema_migrations Tracks which numbered migrations have been applied version, name, applied_at
indexer_cursor Single-row resume state for the poll loop paging_token, last_ledger (highest ledger actually folded), observed_tip_ledger (RPC-observed chain tip — freshness only, kept separate from last_ledger since issue #45), contract_id (cursor is discarded on a cold start if it belongs to a different contract)
events Append-only raw event log — the source everything else is derived from symbol, topics (JSONB), data (JSONB), tx_hash
members Current membership state contribution, stake, has_active_loan, pending_claimed, name (from the registry), defaults_count
loan_proposals Loan votes in flight status (pending/approved/rejected), votes_for, votes_against, voter_count
loans Disbursed loans status (active/repaid/defaulted), total_repayment, outstanding, due_time. id doubles as the originating loan_proposals.id — the contract reuses the proposal's own id for the disbursed loan rather than a separate counter, since a proposal produces at most one loan.
treasury_proposals Treasury withdrawal votes private (routed through commit-reveal instead of open voting), status, votes_for, votes_against, voter_count
notifications Per-address notification feed type, read, indexed on (address, read)
documents Existence/history of a proposal's attached documents (issue #44) — one row per doc_attn event, never the content hash itself proposal_id, kind (loan/treasury — loan and treasury proposal ids collide, drawn from independent sequences), caller, ledger
failed_events Quarantine record for a handler that failed deterministically (issue #43) — additive, never mutates the events row it came from event_id, symbol, ledger, error

On-chain i128 amounts are stored as NUMERIC(40,0) (an i128's max value is ~1.7×10³⁸, which fits under 10³⁹) and returned from the API as decimal strings, never JSON numbers, to avoid silent precision loss — this was in fact a real bug found and fixed during development: pg returns Postgres BIGINT columns as JS strings by default, and the original code assumed they came back as numbers.

Column-type rule for amounts vs. sequences. On-chain i128 amounts are NUMERIC(40,0) and cross the API boundary as strings. Ledger sequence numbers are BIGINT and are returned as JSON numbers — src/db/index.ts registers a BIGINT → number parser scoped to this repo's connection pool, not on the process-wide pg.types registry (a global parser silently truncated any BIGINT above 2⁵³, for every pg consumer in the process). Nothing else should be BIGINT: a token amount stored as BIGINT would be parsed to a number by that pool parser and lose precision above 2⁵³ with no error. Use NUMERIC(40,0) for any new amount column, and only BIGINT for a genuine ledger/sequence value.

Vote tallies are stake-weighted, not a headcount. The contract grants each voter 1 + min(stake / STAKE_WEIGHT_UNIT, MAX_STAKE_BONUS) voting power (currently up to 6) and sums that into for_votes/against_votes. votes_for/votes_against mirror that (hence NUMERIC(40,0), matching the contract's own field width, not a plain vote count); voter_count is the distinct-voter headcount alongside it, so a client can show both "7 members voted" and "carrying 19 voting power." The contract doesn't publish the weight it applied yetloan_vote/tre_vote/revealed currently carry only support — so today every vote folds in as weight 1 regardless of stake, and votes_for/votes_against under-count for any staked voter until the upstream fix lands. The decoder and handlers already read a weight field the moment the contract adds one, with no further backend change needed.

A loan's outstanding balance starts at total_repayment, not the principal. The contract collects total_repayment = amount + interest on repay_loan, so a loan is never worth just its principal from a borrower's perspective. loan_appr doesn't publish total_repayment (only the disbursed amount), so the indexer sources it from the just-approved loan_proposals row instead — loans.id == loan_proposals.id is a documented contract invariant, and that row already carries total_repayment from loan_req/loan_edit. This depends on that proposal row existing, which it will unless the indexer started mid-history; if it's missing, total_repayment falls back to the principal. due_time has the same gap — the contract computes it but doesn't publish it on loan_appr — so it's NULL until that's fixed upstream. GET /api/loans and /api/loans/:id also expose interest_charge and repaid_amount, both derived from total_repayment at read time.

Required fields are validated, not coerced (issue #42). Every handler in src/indexer/handlers.ts reads its decoded fields through either the require* helpers (requireAddr/requireId/requireAmount/requireBool/requireProposalKind) or the older str/num/addr coercion helpers. The require* helpers are for a field a derived row depends on — a missing or malformed one throws instead of silently coercing into a plausible-looking default (a missing amount becoming '0', a bad id becoming NULL and matching zero rows, a non-string address becoming ''). str/num/addr are kept only for genuinely optional fields with no on-chain equivalent yet (weight, due_time) or that no stored row depends on. A thrown FieldValidationError rolls back the write and is handled the same way any other deterministic handler error is — see Quarantine.

Redeploying the contract

The OurDAO contract has no upgrade path — every fix is a fresh deployment with a new CONTRACT_ID. Proposal and loan ids restart at 0 for a new deployment, and loans.id / loan_proposals.id are primary keys, so pointing an existing database at a new contract would silently merge two deployments' state (the new contract's proposal 0 overwriting the old one's under ON CONFLICT (id) DO UPDATE, members' contributions blending, and so on).

The indexer records which contract its cursor belongs to (indexer_cursor.contract_id). When CONTRACT_ID no longer matches, it refuses to start rather than resume. To repoint at a new deployment, choose one:

  • Fresh database (recommended): point DATABASE_URL at a new, empty database. The old deployment's indexed history stays queryable where it is.
  • Reuse the database: start the worker once with INDEXER_RESET_ON_CONTRACT_CHANGE=true. This truncates the cursor and every derived table (members, loan_proposals, loans, treasury_proposals, notifications) and re-indexes the new contract from scratch. The append-only events log is kept — pass ?contract=<C...> to GET /api/events and GET /api/admin/log to scope the raw log to one deployment. Unset the flag again after the first successful boot.

Running one database against multiple contracts simultaneously is deliberately not supported — the derived tables are single-contract by construction.

Event catalog

The full topic-symbol → data-tuple mapping this service decodes (kept in sync with ourdao-contracts's env.events().publish(...) calls):

Symbol Fields Derived-table effect
joined member, fee upserts members, notifies the member
exited member, share marks the member exited
claimed member, pending tracks claimed yield
loan_req id, borrower, amount, total_repayment inserts a pending loan_proposals row
loan_edit proposal_id, borrower, new_amount, total_repayment updates the proposal
loan_vote proposal_id, voter, support, plus a reserved weight not yet published (see above) adds the vote's weight to the tally, bumps voter_count
loan_appr id, borrower, amount, plus a reserved due_time not yet published marks the proposal approved, opens a loans row seeded with total_repayment from the matching proposal (not the bare principal — see below), flags the borrower's has_active_loan
loan_rpy loan_id, borrower, outstanding updates outstanding balance; marks repaid when it hits zero
loan_dflt loan_id, borrower, penalty marks the loan defaulted, slashes the borrower's contribution by the penalty (clamped at zero), bumps defaults_count, clears has_active_loan — idempotent, so redelivering the same event is a no-op past the first application
interest interest, active no per-member breakdown to attribute, but folded into dao_totals.interest_collected and one interest_distributions row (issue #24). interest is interest collected — the contract keeps the indivisible per-member remainder, so it slightly exceeds what members were credited. Per-member yield is still surfaced via claimed.
tre_prop id, amount, destination, private inserts a pending treasury_proposals row
tre_vote id, voter, support, plus a reserved weight not yet published adds the vote's weight to the tally, bumps voter_count
tre_exec id, amount, destination marks the proposal executed, notifies the recipient
staked / unstaked member, amount, new_stake updates the member's stake
name_reg name, owner updates the member's registered name
committed proposal_id, voter notifies the voter their commit was recorded
revealed proposal_id, voter, support, plus a reserved weight not yet published tallies the same as an open vote
doc_attn kind, proposal_id, caller inserts a documents history row (issue #44) — existence/history only; the content hash itself is still read live from the contract via get_document, never indexed
init, admin_add, admin_rem, threshold, policy, paused, unpaused varies admin/governance events — surfaced via /api/admin/log, not folded into a derived table

API reference

Base path: /api.

Method & path Description
GET /health Liveness check + the currently configured contract id. No DB round trip.
GET /ready Readiness probe — checks Postgres reachability and indexer freshness. Returns 503 with a reason when Postgres is down or the indexer cursor is stale.
GET /api/stats Aggregate counts (members, loans, proposals) + defaulted-loan count/value + lifetime money figures (interestCollected, principalLent, principalRepaid, valueDefaulted, all decimal strings) + quarantinedEvents (issue #43) + lastIndexedLedger (highest ledger actually folded) and observedTipLedger (RPC-observed chain tip, issue #45) as the useful "folded to X, chain is at Y" pair. Cached in-process for STATS_CACHE_MS; sets Cache-Control. With more than one API instance the cached figures may briefly disagree.
GET /api/interest Interest-distribution history — one row per interest event (amount collected, active_members at that distribution). ?before=<ledger> cursor.
GET /api/members Active members.
GET /api/members/:address Single member.
GET /api/members/:address/summary Member's dashboard data, including the member row, up to 100 loans, unread notification count, and their relative position to DAO totals (share percentages in basis points) in a single consistent snapshot.
GET /api/proposals/loan Loan proposals with stake-weighted vote tallies (votes_for/votes_against) and a distinct voter_count.
GET /api/loans Loans. Optional ?borrower=, ?before=<id> for pagination. status is active, repaid, or defaulted — a loan is marked defaulted once it's past due plus the policy's grace period (permissionless on-chain, see ourdao-contracts). Each loan includes derived interest_charge and repaid_amount fields.
GET /api/loans/:id Single loan, with the same derived interest_charge/repaid_amount fields.
GET /api/proposals/treasury Treasury proposals with stake-weighted vote tallies and a distinct voter_count.
GET /api/notifications?address= Notifications for an address.
PATCH /api/notifications/:id/read Mark one notification read.
PATCH /api/notifications/read-all?address= Mark every unread notification for an address read.
GET /api/events Raw event feed. Optional ?symbol=, `?before=<id
GET /api/admin/log Admin/governance audit trail — init, admin add/remove, threshold changes, policy changes, pause/unpause.
GET /api/documents?kind=&proposal_id= A proposal's attached-document history (issue #44) — existence/history only, never the content hash (still read live from the contract via get_document). kind (loan or treasury) and proposal_id are both required, since loan and treasury proposal ids are drawn from independent sequences and collide. ?before=<ledger> cursor.
GET /api/admin/failed-events Quarantined events (issue #43) — the operator-facing detail behind /api/stats.quarantinedEvents. Each row has the event id, symbol, ledger, and the error that quarantined it; the raw events row itself is left untouched.

All list endpoints accept ?limit= (default 50, max 200). ?before= and ?after= are cursors: pass the id (or ledger) of the last row you saw to page. For /api/events, the cursor can be a deterministic (ledger, id) value (the event id string itself contains both) and ordering is strictly deterministic (ledger DESC, id DESC by default, or ASC). On-chain i128 amounts are returned as decimal strings to preserve precision (see Database schema); ledger sequence numbers are returned as regular JSON numbers.

Caching

All GET endpoints support ETag and conditional requests (If-None-Match), returning 304 Not Modified when the underlying data is unchanged. Cache-Control headers are set appropriately:

  • Historical immutable queries (/events, /admin/log, /interest, /documents with a ?before= or ?after= cursor) are cached indefinitely (public, max-age=31536000, immutable).
  • Live tip queries use a short TTL (public, max-age=5, must-revalidate).
  • Per-address queries (/notifications, /members/:address/summary) are never cached by shared caches (private, no-cache).

Reorg detection

Stellar's consensus gives fast finality, so a deep reorg is genuinely unlikely — but the indexer now notices one rather than silently folding events from a diverged history (issue #23):

  • The cursor stores last_ledger (and last_ledger_hash, the RPC tip hash at each advance — Soroban's getEvents exposes no per-event ledger hash, so deeper verification isn't possible).
  • Each poll checks continuity: if the RPC's reported latest ledger is below the last folded ledger, or a fetched page contains an event from a ledger already folded past, the indexer halts with a loud log line instead of retrying.
  • Recovery: confirm the true chain state, then run npm run reindex (node dist/indexer/reindex.js in the container). It truncates the derived tables and rebuilds them from the raw events log in one transaction — the log is authoritative and untouched. A rebuild produces state identical to the incremental fold (asserted by a test), so reindex is also the repair path for the historical-data bugs tracked in other issues.
  • Unrecoverable: events that were orphaned and already pruned from the RPC's ~24h window can't be re-fetched; reindex rebuilds from whatever the raw log holds.
  • last_ledger only ever advances to a ledger whose events were actually folded (issue #45). An earlier version fell back to the RPC's reported chain tip on an empty getEvents page, which conflated "highest ledger folded" with "how current the RPC is" — during catch-up, one empty page could jump last_ledger to the tip, and the very next real (but still historically-earlier) page would then look like a rewind and trigger a false halt. The RPC-observed tip is tracked in its own column, observed_tip_ledger — freshness reporting only (/ready, /api/stats.observedTipLedger), never fed into the continuity check above.

Quarantine

A handler bug used to be able to wedge the indexer permanently: ingestPage folds a whole page in one transaction, so one event whose handler throws rolled back the entire page, and the poll loop retried the same page forever behind exponential backoff (capped at POLL_MAX_BACKOFF_MS) — the process stayed up and kept logging the same error, but indexed nothing (issue #43).

  • The poller can't tell a transient failure (RPC hiccup, a DB restart — expected to clear on retry) from a deterministic one (a handler bug, a value that overflows its column) from the error alone. It infers it from repetition: if the same page fails with the same error INDEXER_QUARANTINE_AFTER_FAILURES times in a row (default 3), that's not transient.
  • Once that threshold is hit, the page is retried one event per transaction instead of the whole page at once. Each event's raw log row is written (and stays written) regardless of whether folding it succeeds; if folding throws, that one transaction rolls back and the event is recorded in failed_events (id, symbol, ledger, error) instead — every other event in the page still folds normally, and the cursor advances past all of them.
  • A ReorgDetectedError is never quarantined, on either path — a genuine rewind still halts the indexer immediately, exactly as in Reorg detection above.
  • Once the handler bug is fixed, npm run reindex folds a previously-quarantined event correctly with no extra step — it replays the raw log directly through applyEvent, independent of the poller's quarantine bookkeeping.
  • Quarantined events are visible at GET /api/admin/failed-events and counted in GET /api/stats.quarantinedEvents.

Docker

docker build -t ourdao-backend .
docker run --env-file .env -p 4000:4000 ourdao-backend            # API (default CMD)
docker run --env-file .env ourdao-backend node dist/worker.js      # indexer
docker run --env-file .env ourdao-backend node dist/indexer/reindex.js   # one-off rebuild

The image runs as the non-root node user, uses tini as PID 1, and has a HEALTHCHECK against /health. Both processes migrate on startup, so no separate migrate step is needed.

Testing

# One-time: create the test database (separate from the dev DB above)
docker exec <postgres-container> psql -U ourdao -d postgres -c "CREATE DATABASE ourdao_test;"

npm test          # vitest, against ourdao_test — never touches dev data
npm run lint
npm run typecheck

145 tests across 18 files, covering:

  • Event decode logic (decodeEvent, toJsonSafe) in isolation.
  • Every indexer handler (membership, loan lifecycle including defaults, treasury, staking, registry, commit-reveal privacy, document attachments) against a real Postgres instance — not mocked.
  • Required-field validation per handler (issue #42) and the poller's quarantine path for a deterministically-failing handler (issue #43), including that a genuine reorg is still never quarantined.
  • The last_ledger/observed_tip_ledger split (issue #45): an empty page during catch-up doesn't produce a false reorg halt.
  • Every API route, exercised through a real Fastify instance via .inject().

Tests apply the real schema.sql and truncate all tables between runs (test/db.ts). CI runs all of the above plus npm run build against a Postgres service container on every push and PR (.github/workflows/ci.yml).

Security notes

  • No custody, ever. This service holds no private keys and has no code path that constructs, signs, or submits a transaction. It is a read model over public on-chain events.
  • Fail-soft, not fail-open. If the indexer falls behind or the RPC endpoint is unreachable, reads degrade to stale/empty data (surfaced to the frontend as such) rather than the API crashing or serving incorrect state.
  • CORS is explicit. CORS_ORIGIN defaults to http://localhost:3000 in both code and config — a production deployment should set this to the real frontend origin. Setting it to * is supported as an explicit opt-in but logs a warning at startup.
  • Input handling. All route parameters (addresses, ids, cursors) are validated before being used in parameterized queries — no raw string interpolation into SQL anywhere in the codebase.
  • Rate limiting. Global rate limiting (@fastify/rate-limit) is applied to all API endpoints, with a stricter per-route limit on GET /api/events. Health and readiness probes are exempt. Behind a reverse proxy, set TRUST_PROXY=true so limits apply per client IP. With in-process limiting, the effective global limit is RATE_LIMIT_MAX × instance count.

Status

MVP — the indexer and read API are implemented for the full event catalog, including loan defaults, with test coverage across every indexer handler and API route. Known gaps:

  • Reorg handling is detection only — the indexer halts on a ledger discontinuity and an operator rebuilds derived state from the raw log with npm run reindex (see Reorg detection). There is no automatic rollback and replay of orphaned events.
  • Single indexer instance — no leader-election or multi-instance coordination if you wanted to run more than one worker for redundancy.
  • IPFS pinning for document metadata is a frontend/contract-facing concern (ourdao-frontend's lib/ipfs.ts) — this service indexes doc_attn's existence/history (documents, GET /api/documents) but never the content hash or its content.

Contributing

Contributions are welcome — see CONTRIBUTING.md for local setup, how to run the test suite against a real Postgres, and the backend-specific rules (read-only boundary, append-only event log, transactional event folding). Please claim an issue before opening a pull request.

Found a security vulnerability? Don't open a public issue — use GitHub's private vulnerability reporting on this repo.

License

MIT

About

OurDAO backend — off-chain indexer + read API for the Stellar/Soroban lending DAO.

Resources

Contributing

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages