Kourier Was Right

In 2021 I wanted to show a list: proposals for a DAO, with vote tallies, newest first. All of it was public and on-chain. There was no way to ask for it, because blockchains are bad at lists and this list only mattered to my app.

Hiro's API gets close. Balances, transactions, a contract's events, page by page. "All proposals across three contracts, joined to their votes" isn't an endpoint, and nobody was going to add it for me.

What I did instead:

• fetch each contract's event pages and join them in the browser
• poll so it feels live, until the rate limit
• move the polling to a small server
• watch that server miss an hour of events, with no way to ask what it missed

The other option is running a node and listening to everything it emits. That works and is a part-time job. Both roads end at the same place: rebuilding a database someone already has.

I spent the next three years at Hiro, on the other side of that API. Every serious team had done one of the two.

The proposal

May 2022, Thomas Osmonson (aulneau) publishes Project Kourier. His studio had built stacking.club and the first Gamma, so they'd built this database enough times to be precise about it.

The diagnosis: "Any sufficiently advanced application is going to have unique data requirements that a generalized API like the Hiro API won't be able to solve." The prescription is layers. Raw events at the bottom, fork history settled in the middle, every app deriving its own views on top.

No repository ever appeared. I waited for someone else to build it, same as everyone.

What I built

secondlayer is that stack, running, on your hardware. An indexer sits next to a Stacks node, writes down every event raw, and never edits a row. Everything else is derived from that log into a Postgres you operate.

The list I wanted in 2021 is now one file.

export default defineSubgraph({
  name: "sbtc-flows",
  sources: {
    registry: {
      type: "print_event",
      contractId: "SM3VDXK3WZZSA84XXFKAFAF15NNZX32CTSG82JFQ4.sbtc-registry",
      startBlock: 328228,
    },
  },
  schema: {
    deposits: {
      columns: {
        request_id: { type: "uint", indexed: true },
        amount: { type: "uint" },
        status: { type: "text" },
      },
      uniqueKeys: [["request_id"]],
    },
  },
  handlers: {
    registry: async (event, ctx) => {
      const v = event.value;
      if (v.topic === "completed-deposit") {
        ctx.upsert("deposits", { request_id: v["request-id"] }, {
          amount: v.amount,
          status: "completed",
        });
      }
    },
  },
});

Deploy it and the table fills itself. Backfilled from history, current with the tip, corrected when the chain replaces a recent block.

Reindex

I shipped this subgraph wrong the first time. No backfill.

With a polling wrapper that's a lost weekend. Here it's one command and the handler doesn't change, because the raw log is still there and the table was only ever a view of it.

Rule I keep from this: never let a handler write anything the log can't regenerate.

Three views

Once the log is the source of truth, the products are just views of it.

Kourier's throwaway line was that all of it should be "fully open source, so anyone can run them if they choose". That's the part I took most literally.

docker compose up is the whole stack. The archive makes bootstrap take hours instead of a two-week sync, and you can verify what it hands you. If it vanished tomorrow, your database keeps serving.

Skipped here, separate notes:

• what a raw event looks like on the wire
• how the database stays honest when the chain rewrites its recent past
• what verifying the archive actually proves

Open question: how many replays before 5k events from block 328228 gets slow enough to want snapshots. Haven't hit it yet.