The data your app needs isn't in anyone's API

You're a few weeks into building something on Stacks. The contract works. The wallet connects. Now you want the screen every app eventually needs: the one that shows a user what they've done. Their deposits. Their position over time. Their share of the pool.

So you go looking for that data, and it isn't anywhere.

The public API has transfers. It has blocks and transactions and balances. What it does not have is your pool's positions, because your pool is a contract you deployed last month and nobody has ever heard of it.

The first time this happens it feels like a gap someone forgot to fill. It isn't. It's the shape of the thing.

A public API indexes what's general, the stuff every app on the chain has in common. That's a real service and it's genuinely useful, right up until your question stops being general. Nobody is going to build a positions table for a contract only you deployed, and you wouldn't want them to^¹1. It's the same reason your app has its own database rather than sharing one with every other app on the internet: a schema built for everybody fits nobody in particular.. It'd be a table designed by someone who has never seen your product.

Which leaves you with two options, and they're both worse than they look.

You can usually get close. Pull the raw transfer list, filter it down to your contract, reassemble the shape you wanted in application code, cache it somewhere so the page doesn't take four seconds.

This works, and it keeps working until it doesn't. The reassembly logic grows. Every new screen needs a slightly different slice, so you add another loop over the same raw feed. Then a rate limit shows up in production and you find out how much of your product's reliability was actually somebody else's uptime.

The tell is when your "just fetch the transfers" helper crosses a few hundred lines. At that point you have written an indexer. You just wrote it in the worst possible place, on the request path, without meaning to.

The honest version is a real project. You run a node, or pay someone for access to theirs. You take a firehose of events and turn it into rows. You decode Clarity values into things a database can hold. You backfill years of history before any of it is useful, and then you keep up with the tip forever.

And then there's the part nobody warns you about, which is that the chain can change its mind. Blocks near the tip are provisional; the network occasionally decides the last few were a mistake and continues down a different branch. Your database now contains rows describing events that no longer happened, and undoing them correctly is harder than writing them was.

None of that is about your product. All of it is between you and the screen you wanted to ship.

Secondlayer is that work, already done, running on your hardware.

It sits next to a Stacks node and turns the chain into decoded rows in a database you operate. History comes from a signed archive, so you restore in hours instead of syncing for two weeks. Reorgs roll back cleanly because that's the whole job and we've been wrong about it enough times to get it right.

What you write is the part that's actually yours. Say what table your app needs:

export default defineSubgraph({
  name: "pool-positions",
  sources: {
    deposits: {
      type: "print_event",
      contractId: "SP2....pool",
      topic: "deposit",
    },
  },
  schema: {
    positions: {
      columns: {
        holder: { type: "principal", indexed: true },
        amount: { type: "uint" },
      },
      uniqueKeys: [["holder"]],
    },
  },
  handlers: {
    deposits: (event, ctx) => {
      ctx.increment(
        "positions",
        { holder: event.data.who },
        { amount: event.data.amount },
      );
    },
  },
});

Deploy it and the table fills itself: backfilled from history, current with the tip, correct across reorgs. Then it's a table in Postgres, and you query it the way you query any table you own.

There's one fork, and you already know your answer to it.

I'd rather put that trade in front of you than have you find it in week three. A generated API is a real convenience and it is also somebody else's taste in URLs; if you have opinions about your own, the first path exists precisely so you can keep them.

The runtime is MIT. Running it, indexing forward from your own node, deploying tables, all of it free, and free in the boring sense where there's no tier above it.

What costs money is history. We operate a signed archive of the whole chain, and pulling a large restore or a deep backfill out of it is the metered part. You can verify the archive for free, replay it for free, and check our work against it for free. You pay when you want us to hand you 243 million rows instead of you spending two weeks producing them yourself.

That's the split, and it's deliberate: you're never paying for queries against your own database, because that would be a strange thing to charge for.

It's not a hosted API. There's no endpoint of ours in your critical path. The thing runs on your box, and if we disappeared tomorrow your app would keep serving.

It's not a chatbot or a natural-language layer. You write TypeScript and SQL, same as always.

And it's not a replacement for your database. It fills one.

There's a lot I've skipped. How the archive is signed and what verification actually proves, what happens during a reorg in enough detail to be useful, why Postgres sits underneath all of it. Those are their own posts and I'd rather write them properly than gesture at them here.

But if you've hit the moment I opened with, the screen you can't build because the data isn't anywhere, that's the entire problem this exists to remove. Go run it and see if it holds up.