We reach the end of the module and the lesson that settles the course's oldest debt. Since Module 4, when we set up the first node:http server and sold the first ticket, we have been dragging along a problem that validation did not solve, that well-typed errors did not solve and that not even $inc fully solved: two people buying the last available ticket at the same time. Before attacking it, two pieces every project with a database needs and that almost nobody teaches in time: migrations, so the schema has a history the way the code does, and seeds, to populate the database with one command. With them we will finally retire data/events.json as the source of truth.

1. Migrations: the schema is code too
2. sequelize-cli and Escena Viva's initial migration
3. Migrations over existing data, golden rules and the MongoDB case
4. Seeds: npm run seed
5. Transactions, ACID and the overselling scenario
6. Transactions in Sequelize and isolation levels
7. Pessimistic versus optimistic locking
8. Transactional buyTickets and the MongoDB solution
9. Deadlocks, retries and what not to put inside

Your code has a history: every change is a commit, with an author, a date, a reason and the option to revert it. Your database schema, if you created it with sync() or by hand with psql, has none of that. Nobody knows when that column was added, or why, or what it looked like before. And when a colleague clones the repository, their database looks nothing like yours. A migration is a versioned file, stored next to the code, describing a schema change in two directions: up applies it, down undoes it. They run in order and the database records which ones it has already applied. Three benefits make it non-negotiable: reproducibility (new machine, empty database, one command), review (a schema change goes through a pull request like any other) and automated deployment (the CI/CD of Module 11 runs them before starting the new version).

You install it with npm install --save-dev sequelize-cli and npx sequelize-cli init creates the standard structure: config/ (connections), migrations/, seeders/ and models/. The generated config/config.json is no use to us, because configuration lives in src/config/index.js: we replace it with a config/config.js exporting an object with one key per environment (development, test, production), each with { url: configuration.postgresUrl, dialect: 'postgres' }. A migration is a module with two async functions receiving queryInterface — the API for manipulating the schema — and Sequelize for the types. Sequelize creates a SequelizeMeta table with one row per applied migration: when you run db:migrate it compares the files against that table and applies only the missing ones, in alphabetical order — which is why the names begin with a timestamp.

queryInterface offers createTable/dropTable, addColumn/removeColumn/changeColumn/renameColumn, addIndex and addConstraint (for CHECK, UNIQUE and foreign keys), bulkInsert/bulkUpdate/bulkDelete to move data inside the migration itself, and sequelize.query when none of the above is enough.

The commands are npx sequelize-cli db:migrate (applies the pending ones), db:migrate:status (which ones are applied) and db:migrate:undo (reverts the last one). Weeks later, the business asks to classify events by language. The table already has data: you cannot simply add a NOT NULL column, because the existing rows would have no value and the engine would reject the change. The correct pattern has three steps.

This sequence — add permissively, backfill, tighten — is the universal recipe for mandatory columns on populated tables. Memorize it, and with it the golden rules:

1. Never edit an already applied migration. On your machine you would edit it and re-run it, but in production it is already in SequelizeMeta and will not be re-run, so your change will never get there. Fixes are made with a new migration.
2. Every migration must be reversible. Write the down and test it: a down that does not work is a deployment you cannot back out of at three in the morning.
3. Backwards-compatible changes. In a zero-downtime deployment (Module 11) the old version and the new one coexist for minutes against the same database. If your migration renames a column, the old version breaks instantly. The technique is called expand and contract: add the new nullable column while the new code writes to both, copy the data, deploy code that uses only the new one and, finally, drop the old one.
4. Data and schema, separated when they are large, and test against a copy of production. An UPDATE over ten million rows inside a migration locks the table and leaves the deployment hanging; for that, a separate batched script. And the problems only show up at volume, never on your empty three-row database.

"MongoDB has no schema, therefore it needs no migrations." That is false, and it is one of the most expensive confusions in the industry. The absence of a schema in the engine does not remove the schema: it moves it into your code. If you add language with required: true to the Mongoose schema, the five million existing documents do not have it and any save() on them will fail. If you rename price to priceCents, the old documents keep the old name and your queries will return nothing without raising any error.

A real difference: in SQL the migration changes the structure and the data adapts; in MongoDB the migration is a data transformation. But the discipline — a versioned file, up/down, a record of what has been applied, review in a pull request — is identical.

A seed loads the initial data. Our case is a fond one: the 3 events and 7 sessions of data/events.json, the file that has been with us since Module 3, become the database's initial load. It stops being the store and becomes the seed. It is invoked with npm run seed and, with --reset, it wipes before loading (seed:clean).

Idempotence is what separates a professional seed from a throwaway script: it is achieved with an upsert on the business key, never with an insert. You can run it on every development startup, after every migration and before every demo without fear of duplicating anything. And its value goes beyond development: in Module 9, when we write tests, a deterministic seed is what lets you assert "after selling 2 tickets for ses-001-1, 86 are left", because the starting state is known and reproducible. Combined with an in-memory or per-suite ephemeral database, it makes integration tests fast and isolated. We will develop that there; here it is enough to leave the tool ready.

A transaction is a set of operations the engine treats as a single one. Applied to somebody buying two tickets: atomicity means that reserving capacity, creating the order and issuing two tickets happens all or nothing, with no visible intermediate states — if the second ticket fails, the capacity comes back on its own and the order does not exist; consistency, that on commit every constraint still holds (the CHECK (sold <= capacity), the foreign keys, the NOT NULLs) and that if the result would violate any of them the engine rolls everything back; isolation, that while your transaction is in progress another one does not see your changes half-done; and durability, that when the engine says "committed" it is on disk and a power cut a millisecond later does not undo it. Without transactions, each operation is atomic on its own, but the set is not: that is exactly the problem written in capitals inside createOrder in lesson 07-03. Let's look at it: ses-001-1 has a capacity of 400 and 399 sold, one ticket is left, and Lucía and Marc both hit "buy" in the same second.

Let's analyze why the defenses we already have fail. The prior check is not enough: between step 2 and step 4 there is a window, and the state Marc read is no longer the real one when he writes. Any logic of the form "read, decide, write" has that window, and in Node it is especially easy to open, because every await hands control back to the event loop, which serves Marc precisely in that gap. Making the window smaller does not remove it: it only makes the failure rarer and harder to reproduce, which is worse. $inc is not enough either: it solves the lost update — if both add 1, the result is 401 and not 400, because each addition is applied to the real value — but it adds unconditionally, with no idea that 401 exceeds the capacity; we have swapped an incorrect number for a correct number that reports an overselling that has already happened. And schema validation is not enough either: the Mongoose validator runs when saving a complete document, and in an updateOne with $inc it has access neither to the result nor to capacity; and even if it did, it runs in your process, so two Node processes would validate independently and both would approve.

The solution has to satisfy one condition: the check and the write must be a single indivisible operation for the engine, and there are two ways to get there.

Use the managed one by default; the unmanaged one only if you need savepoints or complex conditional logic. The detail everyone forgets: you must propagate { transaction: t } to every single query. A query without t runs outside the transaction, on another connection, and it will neither see the pending changes nor be rolled back with them. It is the most common and the quietest bug: everything seems to work until something fails and you discover half-written data.

Perfect isolation would mean running transactions one at a time, but that would destroy performance. The levels let you choose how much correctness you pay for in concurrency.

The three anomalies with our example. Dirty read: you read sold = 400 from a transaction that has not committed yet and that will end up rolling back; you decide on data that never existed. Non-repeatable read: you read 399, another transaction commits, you read again and now it is 400 — two reads of the same value in the same transaction give different results, and it is the anomaly that causes our overselling. Phantom read: you count 5 tickets, another transaction inserts one and on recounting there are 6. With { isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE } PostgreSQL detects the conflict and aborts one of the two transactions with a serialization error: that is correct, but it moves the work into your code, which has to retry. For ticket sales there is a simpler and cheaper solution.

Pessimistic: I assume there will be a conflict and I lock the row before touching it. SELECT ... FOR UPDATE reserves it, and any other transaction that wants to lock it waits until I commit or roll back.

In Sequelize you ask for it with lock: t.LOCK.UPDATE. When Marc wakes up, he reads the updated value, 400, sees that nothing is available and fails cleanly with a 409 INSUFFICIENT_CAPACITY. The window has vanished. Optimistic: I assume conflicts are rare, I lock nothing, and I detect on writing whether somebody got there first, by means of a version column incremented on every change; the UPDATE carries the version I read in its where, and if it affects no rows then somebody got ahead of me and I have to retry from scratch.

Which one fits ticket sales. The pessimistic one, without a doubt. Selling has a very characteristic pattern: when tickets go on sale for a long-awaited concert, thousands of people compete for the same row for a few minutes. With optimistic locking most attempts would fail and retry, generating more load at exactly the worst moment and a terrible experience. With pessimistic locking the requests serialize over that row and each one gets a definitive answer first time: either you have a ticket, or there is none left. On top of that the transaction is extremely short, so contention lasts milliseconds. The optimistic one would be the right choice for editing an event's details, where two organizers collide once a month.

The definitive version. Compare it with the one from lesson 07-03, all-caps comment included.

Go back to Lucía and Marc's scenario with this code. Lucía comes in, locks the row, sees 1 available, sells and commits. Marc was waiting at step 1; he wakes up, reads sold = 400, sees 0 available and receives a clean 409 INSUFFICIENT_CAPACITY, with its error code and its message. There is no overselling. There is no half-written state. There is no ticket issued without an order and no order without tickets. The problem we opened in Module 4 is closed. In MongoDB it is solved without a transaction, taking advantage of the fact that an update to a single document is atomic: the key is to put the condition inside the filter.

Why it works: the filter sold <= capacity - quantity and the $inc are evaluated and applied under the engine's own document lock. If Marc arrives after Lucía, his filter does not match and findOneAndUpdate returns null without writing. It is a compare-and-swap operation, the same idea as a compare-and-swap in concurrent programming: fast, transaction-free and with no infrastructure requirements. Its limit: it only covers one document, and the order and the tickets live in other collections. That is what multi-document sessions are for; they are opened with mongoose.startSession() and used with mongoSession.withTransaction(async () => { ... }) — which commits when it finishes, rolls back if it throws and additionally retries transient errors — propagating { session } to every operation, just like { transaction: t } in Sequelize. And an operational requirement that surprises people: MongoDB transactions require a replica set; a standalone mongod on your laptop does not support them and it has to be started with --replSet, even as a single node (on Atlas they come as standard). They also have a cost: they hold snapshots, expire after 60 seconds by default and can abort on a write conflict. The criterion: in MongoDB use the conditional atomic update whenever the problem fits inside one document — our case for capacity, thanks to having embedded the sessions — and save multi-document transactions for when the change unavoidably spans several collections.

A deadlock happens when two transactions wait for each other: Lucía locks session A and wants B; Marc locks B and wants A. PostgreSQL detects it and aborts one with error 40P01. It is avoided by always locking in the same order (for example, sessions sorted by ascending identifier, which makes the cycle impossible), with short transactions and by locking as little as possible. And when it happens anyway, you retry with an increasing wait:

Note the nuance: only transient errors are retried. An INSUFFICIENT_CAPACITY is never retried; it is not a temporary failure, it is an answer. And there are things that must never go inside a transaction. HTTP calls to external services, because a slow service keeps the locks open for seconds, the pool runs dry and the whole application stops. Sending emails, for the same reason and because they are also not reversible: if the transaction rolls back, the email has already gone out. Charges to the payment gateway, which are not undone by a ROLLBACK: you charge before, or you compensate afterwards. Writing files and emitting SalesManager events, which do not take part in the transaction and would make subscribers act on data that might be rolled back. And long loops or heavy computation, which stretch the transaction and the contention. The rule: inside, only database operations, and as few as possible. Everything else goes before (if it must gate the purchase) or after (if it must react to it). In Escena Viva the charge is authorized first, the transaction records the sale, and the confirmation email and the sale-recorded event are emitted after the commit. When that "after" has to be reliable — retries, ordering, guaranteed delivery — it becomes a job queue, which is what we will see with Redis in Module 10.

• Editing an already applied migration. It will not reach the environments that already ran it. Fix it with a new migration.
• Forgetting { transaction: t } on a query, which then runs outside and is not rolled back, or leaving an unmanaged transaction with no rollback in the catch, which holds a pool connection until it expires; with enough of them, the application hangs.
• Trusting the prior check alone. Between reading and writing there is always a window. Lock, or atomic filter.
• Putting an HTTP call inside the transaction, or retrying business errors: the first drains the pool at the first traffic spike and the second fixes nothing, because INSUFFICIENT_CAPACITY does not improve on retry.
• Believing MongoDB needs no migrations. The schema exists all the same; it just lives in your code and in documents that no longer match it.
• Tip: test concurrency for real — 50 simultaneous purchases against a session with 10 tickets — and measure how long your transactions take: any that exceeds 100 ms deserves a review. Without that test you do not know your solution works: you believe it works.

Implement cancelPurchase(orderId) with Sequelize, transactionally: lock the order and its session, check that the status allows cancellation (paid or issued), mark the tickets as cancelled, return the capacity and mark the order as cancelled. Justify the order in which you lock.

Write a script that sets a test session to capacity 10 and sold: 0, fires 50 simultaneous calls to buyTickets for 1 ticket with Promise.allSettled, and verifies that exactly 10 resolve, 40 are rejected with INSUFFICIENT_CAPACITY and sold ends up at 10.

Exercise 1. You lock the order first and the session second; always keeping that same order in every transaction touching both tables is precisely what makes a deadlock impossible.

Exercise 2.

If once every hundred runs it reports 11 successes, you have a race condition; run it several times, because concurrency failures are intermittent by nature and that is exactly why you have to go looking for them on purpose.

Module 7 closes, and with it the JSON file era. We began by understanding why data/events.json had stopped being enough and what a DBMS gives you; we modeled Escena Viva with MongoDB and Mongoose, with its embedded sessions, its validators, its virtuals and its indexes; we wrote the complete CRUD and retired src/catalog-data.js, replacing it with src/repositories/events.js without the controllers noticing; we explored relationships, populate, the N+1 problem and the aggregation framework, which swept away the reports we used to build by reading CSV files; we visited the relational world with PostgreSQL and Sequelize, where a CHECK (sold <= capacity) turns an invariant into a law of the engine and where include performs a real JOIN; and today we have versioned the schema with migrations, loaded the catalog with an idempotent seed — the 3 events and 7 sessions, capacity 3000, 1811 sold — and finally solved the problem we had been dragging along since Module 4.

Because that is what matters: Escena Viva no longer oversells. Neither with SELECT ... FOR UPDATE inside a transaction in PostgreSQL, nor with findOneAndUpdate and its conditional filter in MongoDB. Two engines, two techniques, one and the same guarantee: either the purchase happens whole, or nothing happens. And yet, there is something that should be nagging at you. Look at buyTickets again. It receives a userId… from where? Right now, from whatever the client feels like sending. Anybody can call the API. Anybody can buy on Lucía's behalf, look up Marc's orders, publish an event at Teatro Almendra without being its organizer or cancel a stranger's tickets. There are no real users, no passwords, no sessions, no permissions. The User model has been waiting since lesson 07-02 with its role field — attendee, organizer, administrator — without anybody using it for anything.

In Module 8 we fill that gap: authentication and authorization. User registration and password hashing done properly, sessions and cookies with Passport, JSON Web Tokens, access control based on the roles we have already modeled, and the security best practices that turn an API that works into an API you can trust. The data is already safe from concurrency; now it is time to make it safe from strangers.