hamedandClaude Opus 5
4395eea56e
Section 11 and the third closing rule of the design document: preventing a double booking is the database's job, not the code's. Any "is it free?" check in PHP has a race window between the read and the write — two concurrent requests both see free and both write. MariaDB has no range EXCLUDE constraint, so every occupied interval is broken into fixed five-minute buckets under UNIQUE(resource_id, bucket_at, seat). The code only INSERTs; a rejection from the database *is* the answer. `seat` carries capacity: a three-bed room has seats 0..2, allocation walks upward on each collision, and the fourth concurrent hold finds nowhere to sit. Counting capacity in PHP would have rebuilt the very race this removes. Buckets are written through DBAL rather than the ORM on purpose: a unique violation raised inside flush() closes the EntityManager, and the next seat attempt would then fail with "EntityManager is closed", hiding the real outcome. Occupancy is one row per (segment × resource). The reference test asserts the payoff directly: for a 55-minute appointment of numbing / waiting / laser, the room gets three rows and the operator only two — the operator holds nothing during the wait and stays bookable for someone else. A partial hold never survives. If the second resource has no room, the first is released and the hold itself removed; otherwise a resource stays locked for an appointment that will never exist. Confirming does not re-reserve anything — the seats were taken at hold time and only the label changes. Re-reserving on confirm would reopen the race the hold closed. Cancelling marks rows `released` instead of deleting them, because the history of which resource was busy when is the input to the utilisation reports; the uniqueness buckets *are* deleted, or that interval would stay locked forever. Expired holds are released by the existing scheduler rather than a new one. That exposed a bug in my own change: the flush guard used $count, which now includes released holds, so reset([]) could pass false to save(). It is guarded on $expired. The appointment itself is still built with the existing constructor, so active_slot_key, events and the payment path behave exactly as before — the multi-resource occupancy sits beside them, not instead of them. 12 tests. Two matter most: the second hold on the same resource and interval getting 409, and a test that writes a duplicate bucket row over a *separate connection* and expects the unique-key violation — if that one ever passes silently, the guarantee had moved back into the code. 1208 tests / 3495 assertions. phpstan at its 14-error baseline. Frozen slot contract green. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
52 lines
3.7 KiB
PHP
52 lines
3.7 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace DoctrineMigrations;
|
|
|
|
use Doctrine\DBAL\Schema\Schema;
|
|
use Doctrine\Migrations\AbstractMigration;
|
|
|
|
/**
|
|
* Holds, recorded appointment segments, and the uniqueness guarantee.
|
|
*
|
|
* MariaDB has no range EXCLUDE constraint, so every occupied interval is broken into
|
|
* fixed five-minute buckets and UNIQUE(resource_id, bucket_at, seat) makes an overlap
|
|
* impossible. `seat` expresses capacity: a three-bed room has seats 0..2 and the
|
|
* fourth concurrent booking finds nowhere to sit.
|
|
*
|
|
* Preventing double booking is the database's job, not the code's — any "is it free?"
|
|
* check in PHP has a race window between the read and the write.
|
|
*/
|
|
final class Version20260731054324 extends AbstractMigration
|
|
{
|
|
public function getDescription(): string
|
|
{
|
|
return 'Add appointment holds, recorded segments and occupancy buckets';
|
|
}
|
|
|
|
public function up(Schema $schema): void
|
|
{
|
|
$this->addSql('CREATE TABLE appointment_holds (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, starts_at INT NOT NULL, ends_at INT NOT NULL, expires_at INT NOT NULL, payload JSON NOT NULL, confirmed_at INT DEFAULT NULL, created_at INT NOT NULL, entity_type VARCHAR(10) NOT NULL, entity_id INT NOT NULL, user_id INT NOT NULL, UNIQUE INDEX UNIQ_6905A14BD17F50A6 (uuid), INDEX IDX_6905A14BA76ED395 (user_id), INDEX idx_hold_tenant (entity_type, entity_id), INDEX idx_hold_expiry (expires_at), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');
|
|
$this->addSql('CREATE TABLE appointment_segments (id INT AUTO_INCREMENT NOT NULL, sequence SMALLINT NOT NULL, name VARCHAR(150) NOT NULL, starts_at INT NOT NULL, ends_at INT NOT NULL, patient_present TINYINT DEFAULT 1 NOT NULL, appointment_id INT NOT NULL, INDEX IDX_13EA50E1E5B533F9 (appointment_id), INDEX idx_appointment_segment_seq (appointment_id, sequence), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');
|
|
$this->addSql('CREATE TABLE resource_occupancy_buckets (id INT AUTO_INCREMENT NOT NULL, bucket_at INT NOT NULL, seat SMALLINT NOT NULL, resource_id INT NOT NULL, occupancy_id INT NOT NULL, INDEX IDX_9BE4732989329D25 (resource_id), INDEX idx_bucket_occupancy (occupancy_id), UNIQUE INDEX uniq_bucket_resource_seat (resource_id, bucket_at, seat), PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb4');
|
|
$this->addSql('ALTER TABLE appointment_holds ADD CONSTRAINT FK_6905A14BA76ED395 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE');
|
|
$this->addSql('ALTER TABLE appointment_segments ADD CONSTRAINT FK_13EA50E1E5B533F9 FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE CASCADE');
|
|
$this->addSql('ALTER TABLE resource_occupancy_buckets ADD CONSTRAINT FK_9BE4732989329D25 FOREIGN KEY (resource_id) REFERENCES clinic_resources (id) ON DELETE CASCADE');
|
|
$this->addSql('ALTER TABLE resource_occupancy_buckets ADD CONSTRAINT FK_9BE473298A0BBA84 FOREIGN KEY (occupancy_id) REFERENCES resource_occupancy (id) ON DELETE CASCADE');
|
|
$this->addSql('ALTER TABLE resource_occupancy ADD hold_id INT DEFAULT NULL');
|
|
}
|
|
|
|
public function down(Schema $schema): void
|
|
{
|
|
$this->addSql('ALTER TABLE appointment_holds DROP FOREIGN KEY FK_6905A14BA76ED395');
|
|
$this->addSql('ALTER TABLE appointment_segments DROP FOREIGN KEY FK_13EA50E1E5B533F9');
|
|
$this->addSql('ALTER TABLE resource_occupancy_buckets DROP FOREIGN KEY FK_9BE4732989329D25');
|
|
$this->addSql('ALTER TABLE resource_occupancy_buckets DROP FOREIGN KEY FK_9BE473298A0BBA84');
|
|
$this->addSql('DROP TABLE appointment_holds');
|
|
$this->addSql('DROP TABLE appointment_segments');
|
|
$this->addSql('DROP TABLE resource_occupancy_buckets');
|
|
$this->addSql('ALTER TABLE resource_occupancy DROP hold_id');
|
|
}
|
|
}
|