= P8 – Advanced Application Development (Transactions, Pooling) =

== Overview ==
This phase extends the foundational transaction concepts with concrete, schema-specific examples derived directly from the approved normalized database. Each transaction targets real business scenarios in the Wedding Planner application and references the exact tables from the schema.

In a production environment, these concepts ensure data consistency, correctness, and performance when multiple users interact with the system simultaneously.

== What we cover in this phase ==
In this phase we focus on:

* Database transactions in real application scenarios
* Atomic operations across multiple related tables
* Conflict detection and concurrency handling
* Isolation levels and race conditions
* Savepoints for partial rollback logic
* Connection pooling and performance scaling
* Flask + SQLAlchemy implementation patterns

== Transaction 1 – Full Wedding Bundle Booking ==

=== Business scenario ===
When a user finalises a wedding plan, all services must be booked atomically:
venue, photographer, band, and registrar.

If any single booking fails, none of the changes should be saved.

=== Tables involved ===

* venue_booking (overlap check on venue_id + date)
* photographer_booking (overlap check on photographer_id + date)
* band_booking (overlap check on band_id + date)
* registrar_booking (overlap check on registrar_id + date)
* wedding (update notes after success)

=== Conflict detection logic ===
Two bookings overlap when:
{{{
start_time < new_end_time AND end_time > new_start_time
}}}

=== SQL Transaction ===
{{{
BEGIN;

-- Venue check
SELECT 1 FROM project.venue_booking
WHERE venue_id = 1
AND date = '2026-06-20'
AND start_time < '23:00'
AND end_time > '16:00'
AND status <> 'cancelled';

-- Photographer check
SELECT 1 FROM project.photographer_booking
WHERE photographer_id = 1
AND date = '2026-06-20'
AND start_time < '22:00'
AND end_time > '14:00'
AND status <> 'cancelled';

-- Band check
SELECT 1 FROM project.band_booking
WHERE band_id = 1
AND date = '2026-06-20'
AND start_time < '23:00'
AND end_time > '18:00'
AND status <> 'cancelled';

-- Registrar check
SELECT 1 FROM project.registrar_booking
WHERE registrar_id = 1
AND date = '2026-06-20'
AND start_time < '10:30'
AND end_time > '10:00'
AND status <> 'cancelled';

-- Inserts
INSERT INTO project.venue_booking (...)
VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);

INSERT INTO project.photographer_booking (...)
VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);

INSERT INTO project.band_booking (...)
VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);

INSERT INTO project.registrar_booking (...)
VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);

-- Update wedding
UPDATE project.wedding
SET notes = 'All services confirmed'
WHERE wedding_id = 1;

COMMIT;
}}}

=== Flask implementation ===
{{{
def book_full_wedding(...):
try:
db.session.begin()

```
    _check_overlap(...)
    db.session.add(...)
    db.session.commit()

except Exception:
    db.session.rollback()
```

}}}

== Transaction 2 – Guest Registration with RSVP Seeding ==

=== Business scenario ===
When a guest is added, they must automatically receive a pending RSVP for every event in the wedding.

=== Tables involved ===

* guest (insert)
* event (read all events)
* event_rsvp (insert pending rows)

=== Why transaction is needed ===
Without a transaction, a system crash could leave:

* guest inserted
* but missing RSVP records → inconsistent state

=== SQL Transaction ===
{{{
BEGIN;

INSERT INTO project.guest (...)
VALUES ('Marija','Ilievska','[marija@gmail.com](mailto:marija@gmail.com)',1)
RETURNING guest_id;

INSERT INTO project.event_rsvp (...)
SELECT 'pending', CURRENT_DATE, :new_guest_id, e.event_id
FROM project.event e
WHERE e.wedding_id = 1;

COMMIT;
}}}

=== Flask implementation ===
{{{
db.session.begin()

guest = Guest(...)
db.session.add(guest)
db.session.flush()

events = db.session.query(Event).filter_by(wedding_id=wedding_id).all()

for event in events:
db.session.add(EventRSVP(...))

db.session.commit()
}}}

Key idea: flush() is used to obtain guest_id before commit.

== Transaction 3 – RSVP Acceptance and Seat Assignment ==

=== Business scenario ===
When a guest accepts an RSVP:

* RSVP status updates to accepted
* Attendance row is created or updated

Both must happen together.

=== Tables involved ===

* event_rsvp (update)
* attendance (UPSERT)

=== SQL Transaction ===
{{{
BEGIN;

UPDATE project.event_rsvp
SET status = 'accepted',
response_date = CURRENT_DATE
WHERE guest_id = 1 AND event_id = 2;

INSERT INTO project.attendance (...)
VALUES ('attending', 5, 'Guest', 1, 2)
ON CONFLICT (guest_id, event_id)
DO UPDATE SET
status = EXCLUDED.status,
table_number = EXCLUDED.table_number,
role = EXCLUDED.role;

COMMIT;
}}}

=== Key concept ===
ON CONFLICT prevents race conditions and ensures atomic seat assignment.

== Transaction 4 – Wedding Cancellation with Cascade ==

=== Business scenario ===
Cancelling a wedding must cascade across:

* bookings
* events
* attendance records

Partial cancellation is not allowed.

=== Tables involved ===

* wedding
* venue_booking
* photographer_booking
* band_booking
* registrar_booking
* event
* attendance

=== SQL Transaction ===
{{{
BEGIN;

UPDATE project.wedding
SET notes = 'Wedding cancelled'
WHERE wedding_id = 1;

UPDATE project.venue_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.photographer_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.band_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.registrar_booking SET status='cancelled' WHERE wedding_id=1;

UPDATE project.event SET status='cancelled' WHERE wedding_id=1;

UPDATE project.attendance
SET status='absent'
WHERE event_id IN (
SELECT event_id FROM project.event WHERE wedding_id = 1
);

COMMIT;
}}}

=== Concurrency risk ===
Without a transaction, new RSVP updates could be inserted during cancellation, causing inconsistent state.

== Transaction 5 – Budget Validation with Row Locking ==

=== Business scenario ===
Before finalising a wedding, ensure total cost does not exceed budget.

Must prevent race conditions where new bookings are inserted during validation.

=== Tables involved ===

* wedding (FOR UPDATE lock)
* venue_booking
* photographer_booking
* band_booking
* registrar_booking

=== SQL Transaction ===
{{{
BEGIN;

SELECT budget FROM project.wedding
WHERE wedding_id = 1
FOR UPDATE;

-- Compute total cost (venue + photographer + band + registrar)

IF total_cost > budget THEN
ROLLBACK;
END IF;

UPDATE project.wedding
SET notes = 'Budget validated – wedding finalised'
WHERE wedding_id = 1;

COMMIT;
}}}

=== Why FOR UPDATE matters ===
It prevents TOCTOU (Time-of-check vs Time-of-use) race conditions by locking the wedding row.

== Savepoints – Partial Rollback ==

=== Concept ===
Savepoints allow partial rollback inside a transaction without cancelling everything.

=== Example ===
{{{
BEGIN;

INSERT venue_booking;
INSERT photographer_booking;

SAVEPOINT after_core;

INSERT band_booking;

ROLLBACK TO SAVEPOINT after_core;

COMMIT;
}}}

Core bookings remain, optional upgrade is discarded safely.

== Isolation Levels ==

=== Overview ===
Isolation controls how concurrent transactions interact.

Common problems:

* Dirty reads
* Non-repeatable reads
* Phantom reads
* Lost updates

=== Levels used in this system ===

* READ COMMITTED – simple reads (guest lists, catalog)
* REPEATABLE READ – RSVP operations
* SERIALIZABLE – full booking and budget validation

=== SQLAlchemy setting ===
{{{
db.session.execute(text('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'))
}}}

== Connection Pooling ==

=== Why pooling is needed ===
Without pooling:

* each request opens a new DB connection
* high overhead and slow response

With pooling:

* connections are reused
* better scalability and stability

=== Key endpoints affected ===

* /availability/*
* /rsvp
* /weddings/<id>/book-all
* /guests

=== Pool configuration ===
{{{
engine = create_engine(
'postgresql://user:pass@localhost/weddingdb',
pool_size=10,
max_overflow=20,
pool_timeout=30,
pool_recycle=1800,
pool_pre_ping=True
)
}}}

=== Parameter meaning ===

* pool_size – base connections
* max_overflow – burst capacity
* pool_timeout – wait time for connection
* pool_recycle – avoid stale DB connections
* pool_pre_ping – detect dead connections

== Conclusion ==
This phase introduced advanced database reliability and scalability concepts applied directly to the Wedding Planner system.

Key outcomes:

* Transactions ensure atomic multi-table operations
* Concurrency control prevents race conditions and inconsistencies
* Isolation levels protect against anomalies
* Savepoints allow partial rollback for optional features
* Connection pooling improves performance under load

These mechanisms are essential for production-grade backend systems handling concurrent users and complex relational workflows.