Changing Iokeep's database was not mainly a schema exercise. The difficult part was that persistence details had spread into the service layer.
The original Express backend imported Mongoose models directly. Services created ObjectId values, mutated live documents, called .save(), and used model-specific query methods. Replacing MongoDB under that design meant rewriting business logic at the same time as the storage layer.
I used the migration to PostgreSQL to introduce a boundary that had been missing.
The Constraint: Change Storage, Not the Frontend Contract
Iokeep's Next.js frontend already depended on a specific HTTP shape:
- resources exposed
_idstrings; - auth used
access_tokenandrefresh_tokencookies; - folders contained a computed note count;
- notes carried a serialized Lexical state and a two-line description;
- existing routes and status-code quirks were already encoded in client code.
I wanted PostgreSQL internally without forcing a coordinated frontend rewrite. That made API compatibility a migration requirement, not an afterthought.
The target flow became:
Controller
↓
Service factory
↓
Repository interface
↓
Prisma implementation
↓
PostgreSQLControllers still own HTTP translation. Services operate on plain TypeScript types. Prisma is confined to concrete repositories and the database composition root.
Repository Interfaces as the Migration Seam
I created one persistence contract for each domain: users, folders, and notes.
A note repository, for example, exposes operations such as create, findByFolder, findById, update, delete, and countByFolderIds. Its interface uses domain types rather than generated Prisma types.
Services became factory functions that receive those interfaces:
function createNoteService(
noteRepo: INoteRepository,
folderRepo: IFolderRepository
) {
// business operations use the contracts, not Prisma
}A single composition root constructs the Prisma repositories and injects them into the feature services.
This does not make every future database swap free. PostgreSQL arrays, relational constraints, transaction behavior, and query capabilities still shape the implementation. What it does provide is a clear blast radius: database-specific code has a known home, and service tests can use substitutes without importing an ORM client.
Mapping a Document Model to Relations
The migration made several implicit rules explicit.
IDs became UUIDs
MongoDB's ObjectId values became Prisma-managed UUID strings. Internally the domain uses id; response mappers continue exposing _id so the frontend treats identifiers as opaque strings and does not need to change.
That small translation layer was cheaper and safer than leaking a storage migration through every client type.
Ownership became a foreign key
Folder.ownerId now references User.id, and Note.folderId references Folder.id. Both relations use cascading deletes:
user deletion → folders deleted → notes deleted
folder deletion → notes deletedThe earlier document model could leave orphaned notes when a folder disappeared. PostgreSQL now enforces the lifecycle rule instead of relying on every call site to remember cleanup.
Folder names gained a database invariant
The rule "a user cannot have two folders with the same name" is represented by a composite unique constraint on (ownerId, folderName).
The service still performs a friendly pre-check, while the database remains the final authority under concurrent requests.
Arrays stayed arrays—for now
Note.description and User.refreshTokens map to PostgreSQL text arrays. That preserved the existing behavior with minimal API change.
It is a pragmatic bridge, not necessarily the final session model. Refresh tokens stored in a user-level array lack device metadata and can grow with repeated logins until individual tokens are removed. A dedicated session table would support expiry, device labels, revocation history, and indexed lookup more cleanly.
Preserving the API With Response Mappers
Prisma records use id; Iokeep's API uses _id. Rather than rename the public contract during the database migration, feature services map persistence records to API-facing objects.
The note create path also returns a summary without editorState, while single-note and list reads include the full serialized document. Keeping these response shapes stable allowed the frontend's create/update transition and SWR cache logic to keep working.
Compatibility included awkward behavior too. Some authentication failures return HTTP 200 with success: false, and some missing resources return 500 instead of 404. I preserved those behaviors during the storage change so failures could be attributed to one dimension at a time.
That is a migration tactic, not an endorsement. Once the new persistence path is stable, those semantics should be corrected behind explicit contract versioning or coordinated client changes.
Query Translation Revealed Existing Debt
A database migration is useful because it forces every persistence behavior into view.
Removing a double delete
The old folder deletion flow contained overlapping deletion paths. The Prisma repository now performs one deleteMany operation and reports whether a row was removed. Database cascades handle child notes.
Replacing an N+1 note-count loop
The MongoDB implementation counted notes once per folder. The PostgreSQL repository instead groups notes by folderId in one query and returns a map of counts.
The service fills missing entries with zero before returning folders to the client.
folders + one grouped count query
instead of
folders + one count query per folderThis is a small endpoint, but it demonstrates why repository APIs should describe the operation the application needs rather than mirror low-level CRUD methods mechanically.
Avoiding exception-driven existence checks
For updates and deletes, the repositories use updateMany and deleteMany, inspect the affected-row count, and translate zero rows into a domain-level missing-resource result. That keeps Prisma's exception behavior out of the service contract.
Verification at the HTTP Boundary
The backend repository includes an OpenAPI description and an HTTP integration suite that starts the Express application on an ephemeral port against PostgreSQL.
The test client implements a small cookie jar because Node's fetch does not behave like a browser session automatically. It captures both HttpOnly authentication cookies from Set-Cookie headers and replays them on guarded requests.
The suite exercises:
- health reporting;
- signup and login failure variants;
- access-token verification and minting from a refresh token;
- authenticated folder creation, listing, counting, update, and deletion;
- note creation, reads, updates, and deletion;
- logout and cookie invalidation.
A separate Playwright journey in the frontend covers signup, login, folder creation, and note creation through the browser UI.
These tests are more valuable than checking repository methods alone. The migration's promise was to preserve observable behavior, so the strongest checks sit at the HTTP and browser boundaries.
What the Audit Says Is Still Unfinished
The rewrite improved persistence structure, but it did not turn an alpha application into a production-ready service.
Authentication is not complete authorization
Routes verify signed cookies, but resource-level owner checks are not yet comprehensive for every folder and note mutation. Before a production release, each operation must prove that the authenticated user owns the target resource, except for an intentionally public read.
Public-resource routing needs hardening
The unauthenticated GET bypass parses originalUrl manually. The integration test captures a query-string edge case where that path comparison does not behave as intended. Route metadata or dedicated public handlers would be safer than string splitting.
Cookie policy needs production settings
The cookies are HttpOnly, but production deployment should explicitly define Secure, SameSite, path, domain, and cross-origin behavior. Token expiry and server-side session lifetime also need one coherent policy.
Error semantics need a cleanup pass
Duplicate resources, missing resources, invalid credentials, and invalid tokens should use consistent 4xx responses. Internal errors should not be the mechanism for ordinary domain outcomes.
Calling these out matters. The repository boundary solves persistence coupling; it does not solve authorization, session design, or API semantics by association.
What I Took Away
The most useful part of this migration was not replacing one database library with another. It was separating three contracts that had been blended together:
- the HTTP contract consumed by the frontend;
- the domain operations implemented by services;
- the persistence operations implemented by repositories.
That separation let me change IDs, add relational constraints, introduce cascades, and improve a grouped query while keeping the browser integration intact.
Because Iokeep was still in alpha, I chose a fresh PostgreSQL database rather than migrating live user data. In a production cutover, I would also need dual-read or validation tooling, row-count and checksum reconciliation, rollback criteria, and a rehearsed migration window.
The honest result is narrower and more useful: I completed a storage-engine rewrite with a controlled compatibility boundary, and the process produced a clearer list of what must be fixed before the system can be treated as production-ready.
