OurDAO Backend

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
• Quick start
• Configuration
• Database schema
• Event catalog
• API reference
• Testing
• Security notes
• Status
• Contributing
• License

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_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, 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)

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 yet — loan_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.

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	(raw log only — the content hash itself is read live from the contract, not 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) + the last indexed ledger. 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/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=<ledger>.
GET /api/admin/log	Admin/governance audit trail — init, admin add/remove, threshold changes, policy changes, pause/unpause.

All list endpoints accept ?limit= (default 50, max 200). ?before= is a cursor: pass the id/ledger of the last row you saw to page further back. On-chain i128 amounts are returned as decimal strings to preserve precision (see Database schema); ledger sequence numbers are returned as regular JSON numbers.

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.

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

48 tests across 8 files, covering:

• Event decode logic (decodeEvent, toJsonSafe) in isolation.
• Every indexer handler (membership, loan lifecycle including defaults, treasury, staking, registry, commit-reveal privacy) against a real Postgres instance — not mocked.
• 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), not something this service indexes today beyond the raw doc_attn event.

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