- Feature Name: core-reservation-service
- Start Date: 2022-10-08 09:33:54
A core reservation service that solves the problem of reserving a resource for a period of time. We leverage postgres EXCLUDE constraints to ensure that only one reservation can be made for a given resource at a given time.
We need a common solution for various reservation requirements: 1) calendar booking; 2) hotel/room booking; 3) meeting room booking; 4) parking lot booking; 5) etc. Repeatedly building features for these requirements is a waste of time and resources. We should have a common solution that can be used by all teams.
Basic architecture:
We would use gRPC as a service interface. Below is the proto definition:
enum ReservationStatus {
UNKNOWN = 0;
PENDING = 1;
CONFIRMED = 2;
BLOCKED = 3;
}
enum ReservationUpdateType {
UNKNOWN = 0;
CREATE = 1;
UPDATE = 2;
DELETE = 3;
}
message Reservation {
string id = 1;
string user_id = 2;
ReservationStatus status = 3;
// resource reservation window
string resource_id = 4;
google.protobuf.Timestamp start = 5;
google.protobuf.Timestamp end = 6;
// extra note
string note = 7;
}
message ReserveRequest {
Reservation reservation = 1;
}
message ReserveResponse {
Reservation reservation = 1;
}
message UpdateRequest {
string note = 2;
}
message UpdateResponse {
Reservation reservation = 1;
}
message ConfirmRequest {
string id = 1;
}
message ConfirmResponse {
Reservation reservation = 1;
}
message CancelRequest {
string id = 1;
}
message CancelResponse {
Reservation reservation = 1;
}
message GetRequest {
string id = 1;
}
message GetResponse {
Reservation reservation = 1;
}
message QueryRequest {
string resource_id = 1;
string user_id = 2;
// use status to filter result. If UNKNOWN, return all reservations
ReservationStatus status = 3;
google.protobuf.Timestamp start = 4;
google.protobuf.Timestamp end = 5;
}
message ListenRequest {}
message ListenResponse {
int8 op = 1;
Reservation reservation = 2;
}
service ReservationService {
rpc reserve(ReserveRequest) returns (ReserveResponse);
rpc confirm(ConfirmRequest) returns (ConfirmResponse);
rpc update(UpdateRequest) returns (UpdateResponse);
rpc cancel(CancelRequest) returns (CancelResponse);
rpc get(GetRequest) returns (GetResponse);
rpc query(QueryRequest) returns (stream Reservation);
// another system could monitor newly added/confirmed/cancelled reservations
rpc listen(ListenRequest) returns (stream Reservation);
}We use postgres as the database. Below is the schema:
CREATE SCHEMA rsvp;
CREATE TYPE rsvp.reservation_status AS ENUM ('unknown', 'pending', 'confirmed', 'blocked');
CREATE TYPE rsvp.reservation_update_type AS ENUM ('unknown', 'create', 'update', 'delete');
CREATE TABLE rsvp.reservations (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
user_id VARCHAR(64) NOT NULL,
status rsvp.reservation_status NOT NULL DEFAULT 'pending',
resource_id VARCHAR(64) NOT NULL,
timespan TSTZRANGE NOT NULL,
note TEXT,
CONSTRAINT reservations_pkey PRIMARY KEY (id),
CONSTRAINT reservations_conflict EXCLUDE USING gist (resource_id WITH =, timespan WITH &&)
);
CREATE INDEX reservations_resource_id_idx ON rsvp.reservations (resource_id);
CREATE INDEX reservations_user_id_idx ON rsvp.reservations (user_id);
-- if user_id is null, find all reservations within during for the resource
-- if resource_id is null, find all reservations within during for the user
-- if both are null, find all reservations within during
-- if both set, find all reservations within during for the resource and user
CREATE OR REPLACE FUNCTION rsvp.query(uid text, rid text, during: TSTZRANGE) RETURNS TABLE rsvp.reservations AS $$ $$ LANGUAGE plpgsql;
-- resevation change queue
CREATE TABLE rsvp.reservation_changes (
id SERIAL NOT NULL,
reservation_id uuid NOT NULL,
op rsvp.reservation_update_type NOT NULL,
);
-- trigger for add/update/delete a reservation
CREATE OR REPLACE FUNCTION rsvp.reservations_trigger() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
-- update reservation_changes
INSERT INTO rsvp.reservation_changes (reservation_id, op) VALUES (NEW.id, 'create');
ELSIF TG_OP = 'UPDATE' THEN
-- if status changed, update reservation_changes
IF OLD.status <> NEW.status THEN
INSERT INTO rsvp.reservation_changes (reservation_id, op) VALUES (NEW.id, 'update');
END IF;
ELSIF TG_OP = 'DELETE' THEN
-- update reservation_changes
INSERT INTO rsvp.reservation_changes (reservation_id, op) VALUES (OLD.id, 'delete');
END IF;
-- notify a channel called reservation_update
NOTIFY reservation_update;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER reservations_trigger
AFTER INSERT OR UPDATE OR DELETE ON rsvp.reservations
FOR EACH ROW EXECUTE PROCEDURE rsvp.reservations_trigger();Here we use EXCLUDE constraint provided by postgres to ensure that on overlapping reservations cannot be made for a given resource at a given time.
CONSTRAINT reservations_conflict EXCLUDE USING gist (resource_id WITH =, timespan WITH &&)
We also use a trigger to notify a channel when a reservation is added/updated/deleted. To make sure even we missed certain messages from the channel when DB connection is down for some reason, we use a queue to store reservation changes. Thus when we receive a notification, we can query the queue to get all the changes since last time we checked, and once we finished processing all the changes, we can delete them from the queue.
TBD
N/A
N/A
N/A
- how to handle repeated reservation? - is this more ore less a business logic which shouldn't be put into this layer? (non-goal: we consider this is a business logic and should be handled by the caller)
- if load is big, we may use an external queue for recording changes.
- we haven't considered tracking/observability/deployment yet.
- query performance might be an issue - need to revisit the index and also consider using cache.
TBD