Improve our control over the `DATABASE_SCHEMA` env var

[lightwalker-eth]

Creating this issue as a placeholder / reminder for a more detailed deep dive in the future into how we're using the DATABASE_SCHEMA env var and devops procedures we can anticipate that may be related to this env var.

FYI the database schema variable is mostly for ponder indexer to avoid clobbering each other (for example when deploying a new version of the indexer pointing to the same db, it should index to a new schema, and then the hosting provider can swap instances when the healthcheck is ready — that's why the default example is the RAILWAY_DEPLOYMENT_ID or whatever. we were also using schema to separate the different plugins, but now that we're unifying them we'll continue to use DATABASE_SCHEMA in the ponder-specific way

Please check the current PG database we have on Railway. You’ll see there’s multiple schemas already, and this number will just keep growing. Ponder is currently creating a new PG schema each time we push to the assigned branch based on the way we have Railway configured. There’s a number of opportunities for optimisation here. First, we lack garbage collection solutions for old schemas. We also ideally could decide which commits should trigger a full rebuild of Ponder’s state in a new schema. And related to this: We need a strategy to maintain uptime when we want to deploy updated indexer logic.

[lightwalker-eth]

Noting down an important detail. For background: We're currently deploying to Railway and using the following command when deploying an instance there: pnpm --filter ensnode start --schema $RAILWAY_DEPLOYMENT_ID.

We discovered another issue with this approach while working on #69. Just modifying an environment variable in Railway (such as an RPC url) causes Railway to change the $RAILWAY_DEPLOYMENT_ID, which then results in requiring a completely new reindexing of all events! Very inconvenient.

It would be good to explore alternative strategies (not using $RAILWAY_DEPLOYMENT_ID) for controlling the ENSNode schema.

[lightwalker-eth]

@tk-o @shrugs @BanaSeba Hey guys, does Ponder provide a method for us to do something approximating the following?

1. Include some steps in our GitHub Actions CI that:
   1. Builds a "signature" of our indexing logic / schema.
   2. Exports that "signature" to a "schema id" file that is tracked directly in our repo.
   3. When an instance of ENSNode starts up, it stops using the --schema command line argument to determine the schema that is used, but instead references the "signature" saved in a "schema id" file.
2. Perhaps some refinements of how we approach building Docker containers?

I assume I didn't write the above in the most elegant way. Please attack all of the above and appreciate your suggestions for better ways we might define our goals / proposed next steps here.

[tk-o]

I was thinking of calculating a compound hash from:

• configuration object (i.e. env vars)
• codebase (i.e. build hash)

I shared my idea on Ponder TG channel:

I'd like to calculate one hash from env vars (config hash) and the other one from codebase (build hash) to use them together for schema name. I need that to handle feature flags where some of the event handlers are loaded conditionally.

Also, sometimes I'm doing just renames or refactorings that don't change the logic of event handlers, and then I'd like to tell ponder runtime that even though there's new build id, it should just carry on with the selected schema name.

Haven't received any answer, so just followed up.