Replacing Payload with Appwrite: what flat columns cannot hold

Payload earned its place in this project. A real admin panel for free, access control as a first-class concept, migrations, uploads. The ownership work in the first entry was only tractable because access rules were something you could write down and test.

What it cost was a Postgres container running next to the app. One more thing to run, patch, monitor and back up, for a personal tool with one real user and a handful of friends.

Appwrite holds the rows, the 2,648 media files and the accounts in one place. The app container becomes stateless, and there is nothing on the server to back up. That was the whole argument, and the migration took a day.

What moved cleanly, and what did not

Tables, the storage bucket and the indexes live in the Appwrite project. The app talks to it through the server SDK. Most collections mapped straight across.

Two shapes did not.

Programme days were nested arrays two levels deep: a programme has days, a day has exercises, each exercise has a target weight and a rep scheme. Appwrite columns are flat. Two child tables would have bought ordering guarantees and referential integrity that this app never uses, so the whole structure is stored as JSON in one column and the referenced exercises are hydrated on read against the catalogue's four digit id.

That is the pragmatic answer rather than the pure one, and it is worth being honest about which it is. The check on it: the day checklist and the exercise references still work end to end, which is what the structure exists for.

Body scans had two nested groups of five segment numbers each, from an InBody sheet. Those are ten flat columns now, folded back into their two groups on read. Ugly in the schema, invisible above the data layer.

Auth, and whose cookie it is

Auth moved to Appwrite Account. Signing in creates a session and puts its secret in a first-party HttpOnly cookie on our own domain.

The reason for handling the cookie rather than letting the SDK do it is that Appwrite's own session cookie is bound to the Appwrite domain, which is a different host from the app. A cookie set there is not sent to us. So the server action takes the session secret and sets its own cookie, and the session client reads it back:

const secret = (await cookies()).get(SESSION_COOKIE)?.value;
if (!secret) return null;              // signed out is a normal state, not an error
return base().setSession(secret);

The security model changes shape, not strength

This is the part that needed the most care, and the part that would be easiest to get quietly wrong.

Payload enforced access control on every read. Appwrite's server SDK authenticates with an API key, and an API key is not a user. It bypasses row permissions entirely. Nothing scopes a read for you.

So every query against private data has to constrain the owner itself. That is not a new rule, it is the rule from the ownership work in the first entry, except that now it is the only thing standing between two people's training rather than a second layer behind access control.

A set of access-control tests was deleted in this migration, and deleting tests without replacing the guarantee is how a regression ships six weeks later. They were replaced with a source guard: a test that reads the query layer and fails the build when a query touching a private table is written without an owner constraint. It catches the case access control used to catch, at the point where the mistake is actually made.

The catalogue and its media stay publicly readable, because they are dataset content rather than anyone's training.

Three query shapes only a live instance tells you about

The migration compiled and typechecked before any of these surfaced.

equal on an array column is rejected outright. The muscle filter matches against a list of muscles per exercise, which was an equality check. It uses contains now.

A fulltext search inside an or group returns a server error. The free-text box searched name and equipment together. It is three contains clauses instead, which turns out to be closer to the like it replaced anyway: typing "barb" now finds barbell work, which a fulltext index would not have done.

Cursor paging is strictly sequential. The helper that reads every row walked pages on a cursor, because each request needs the last id from the one before it. Fourteen round trips in series put the muscle picker over a five second test timeout. The first page now reports the total, and every remaining page is fetched in parallel off that number.

One more, from the storage side: image transformations are disabled on the media bucket. Appwrite's preview endpoint returns a still frame for an animated GIF, and the animation is the entire point of the dataset.

The config that was frozen into the build

This one would have taken the production deploy down and looked like a network problem.

Media URLs are built in a module that client components import as well as pages, so the endpoint and project id were exposed as NEXT_PUBLIC_ variables. That is the documented way to share a value with the browser.

Next inlines NEXT_PUBLIC_ variables at build time. The production image is built on the server by Compose, where the real values only exist once the container starts. So the endpoint was frozen to the build-time placeholder, and every media URL in production would have pointed at http://build-only.

Confirmed by grepping the built chunks before and after the fix, rather than reasoning about it.

The fix is to rename them to plain APPWRITE_* and read them at run time. That is safe here because every caller of the URL builder is a server component. A client component that wants a media URL takes it as a prop, which is the correct shape anyway.

The same commit dropped what the server no longer needs. Seeding runs from a laptop against the Appwrite instance, so the 137 MB of media never has to reach the app server at all, and the media sync step and the bind mount are gone. deploy and up now pass --remove-orphans, without which the Postgres container that was dropped from the stack keeps happily running.

The error handler that sent people to reset the wrong password

Sign-in caught everything and reported it as a wrong password.

That is a comfortable default and an actively harmful one. It sends someone to reset a password that was never the problem, and if Appwrite is unreachable it turns an outage into an apparent wall of failed logins with no signal about the real cause.

Now only a 401 says the password was wrong. A 429 says rate limited. Anything else says the app could not reach Appwrite, and logs the exception.

The same pattern was in the current-user lookup, where it was worse: any failure was treated as "not signed in", so an outage looked like everybody being logged out at once. It logs anything that is not a 401 now.

Alongside it, a colour bug worth mentioning because it is a theming trap rather than a CSS one. The login page still used the inverting primary band while its inputs had moved onto the chrome surface. In dark mode primary flips to near-white, so the form was white text and white borders on a white panel. It uses chrome now, which holds its colour in both modes, and ships both logo lockups with one hidden, because each has its background baked into the SVG.

Which Appwrite package, and why

Worth stating plainly, because the two packages look interchangeable and are not.

node-appwrite is the server SDK. It supports setKey for an API key alongside setSession for a user session, which is what the admin client and the session client both need.

appwrite is the web SDK. Browser only, no API key support.

Every Appwrite call in this app is in a server component, a server action or the seeder. No client component touches it. Installing the web SDK as well would add a second SDK that nothing calls. It becomes worth adding the day something needs live subscriptions or a direct browser upload, and not before.