= 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 a real business scenario 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.

== Transaction 1 – Full Wedding Bundle Booking ==
=== Business Scenario ===
When a user finalises their wedding plan, all four service providers must be booked atomically: venue, photographer, band, and registrar. If any single booking fails (conflict, capacity, data error), none should be persisted.

=== Tables Involved ===
• venue_booking – insert + time-overlap check on venue_id + date
• photographer_booking – insert + overlap check on photographer_id + date
• band_booking – insert + overlap check on band_id + date
• registrar_booking – insert + overlap check on registrar_id + date
• wedding – update notes after all bookings succeed

=== Conflict Detection Logic ===
All four booking tables share the same overlap predicate. Two time windows overlap when:

start_time < $new_end_time AND end_time > $new_start_time

=== Full Transaction – SQL ===
{{{
BEGIN;

```
• STEP 1: Check venue availability
```

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';
-- If any row returned → ROLLBACK (conflict)

```
• STEP 2: Check photographer availability
```

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';

```
• STEP 3: Check band availability
```

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';

```
• STEP 4: Check registrar availability
```

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';

```
• STEP 5: Insert all four bookings
```

INSERT INTO project.venue_booking
(date, start_time, end_time, status, price, venue_id, wedding_id)
VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);

INSERT INTO project.photographer_booking
(date, start_time, end_time, status, photographer_id, wedding_id)
VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);

INSERT INTO project.band_booking
(date, start_time, end_time, status, band_id, wedding_id)
VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);

INSERT INTO project.registrar_booking
(date, start_time, end_time, status, registrar_id, wedding_id)
VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);

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

COMMIT;
-- On any error at any step: ROLLBACK;
}}}

=== Flask Implementation (SQLAlchemy & Python) ===
{{{
from sqlalchemy.exc import SQLAlchemyError

def book_full_wedding(wedding_id, venue_cfg, photo_cfg, band_cfg, reg_cfg):
try:
db.session.begin()

```
    # Conflict checks – raise ValueError if any overlap found
    _check_overlap('venue_booking',        'venue_id',        venue_cfg)
    _check_overlap('photographer_booking', 'photographer_id', photo_cfg)
    _check_overlap('band_booking',         'band_id',         band_cfg)
    _check_overlap('registrar_booking',    'registrar_id',    reg_cfg)

    # Inserts
    db.session.add(VenueBooking(**venue_cfg,  wedding_id=wedding_id))
    db.session.add(PhotographerBooking(**photo_cfg, wedding_id=wedding_id))
    db.session.add(BandBooking(**band_cfg,    wedding_id=wedding_id))
    db.session.add(RegistrarBooking(**reg_cfg, wedding_id=wedding_id))

    db.session.query(Wedding).filter_by(wedding_id=wedding_id)\
        .update({'notes': 'All services confirmed'})

    db.session.commit()
    return {'status': 'ok', 'wedding_id': wedding_id}

except (ValueError, SQLAlchemyError) as e:
    db.session.rollback()
    return {'status': 'error', 'reason': str(e)}, 409
```

}}}

== Transaction 2 – Guest Registration with Automatic RSVP Seeding ==
=== Business Scenario ===
When a new guest is added to a wedding, they must immediately receive a pending RSVP record for every existing event of that wedding (ceremony, registry, wedding etc.). This guarantees no event is orphaned without a guest decision, and no guest can appear in attendance without a prior RSVP.

=== Tables Involved ===
• guest – insert the new guest record
• event – read all events for this wedding_id
• event_rsvp – insert one pending RSVP per event (enforced by uq_rsvp: guest_id + event_id)

=== Why a Transaction is Required ===
Without a transaction, a crash would happen after inserting the guest but before inserting the RSVPs, leaving a guest with no RSVP rows. The UNIQUE constraint on event_rsvp (guest_id, event_id) prevents duplicates but cannot help if the insert never happened.

=== Full Transaction – SQL ===
{{{
BEGIN;

```
• STEP 1: Insert new guest
```

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

```
• STEP 2: Insert a 'pending' RSVP for every event of this wedding
```

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

COMMIT;
-- Error: ROLLBACK;
}}}

=== Flask Implementation ===
{{{
def register_guest(wedding_id, first_name, last_name, email):
try:
db.session.begin()

```
    guest = Guest(first_name=first_name, last_name=last_name,
                  email=email, wedding_id=wedding_id)
    db.session.add(guest)
    db.session.flush()

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

    for event in events:
        rsvp = EventRSVP(
            status='pending',
            response_date=date.today(),
            guest_id=guest.guest_id,
            event_id=event.event_id
        )
        db.session.add(rsvp)

    db.session.commit()
    return {'guest_id': guest.guest_id, 'rsvps_created': len(events)}

except SQLAlchemyError as e:
    db.session.rollback()
    return {'error': str(e)}, 500
```

}}}

Key pattern: db.session.flush() is used to obtain the new guest_id from the database sequence before the transaction commits, so it can be used in the RSVP inserts within the same transaction.

== Transaction 3 – RSVP Acceptance and Seat Assignment ==
=== Business Scenario ===
When a guest accepts an RSVP, the system must simultaneously update event_rsvp to accepted and create or update the attendance record with a table number. These two operations are inseparable: an accepted RSVP with no attendance row causes inconsistency.

=== Tables Involved ===
• event_rsvp – update status from pending to accepted
• attendance – UPSERT the row for this guest_id + event_id using the uq_attendance constraint

=== Full Transaction ===
{{{
BEGIN;

```
• STEP 1: Update RSVP status
```

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

```
• STEP 2: Upsert attendance record
```

INSERT INTO project.attendance
(status, table_number, role, guest_id, event_id)
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;
-- Error: ROLLBACK;
}}}

=== Key pattern ===
The attendance table has UNIQUE (guest_id, event_id). A guest may need to be reassigned to a different table later. Using ON CONFLICT DO UPDATE handles both the initial seat assignment and any re-assignment atomically, avoiding race conditions.

== Transaction 4 – Wedding Cancellation with Status Cascade ==
=== Business Scenario ===
When a wedding is cancelled, every resource tied to it must be cancelled atomically: all service bookings (venue, photographer, band, registrar), all events, and all attendance records. A partial cancellation would leave inconsistent state.

=== Tables Involved ===
• wedding – update status/notes
• venue_booking – cancel bookings
• photographer_booking – cancel bookings
• band_booking – cancel bookings
• registrar_booking – cancel bookings
• event – cancel events
• attendance – mark attendance absent

=== Full Transaction – SQL ===
{{{
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;
-- Error: ROLLBACK;
}}}

=== Concurrency Risk Without Transaction ===
A concurrent RSVP or attendance insert could occur between steps, causing inconsistent data tied to a cancelled wedding.

== Transaction 5 – Budget Validation with Row Locking ==
=== Business Scenario ===
Before finalising a wedding, the system must ensure total cost does not exceed budget. The check and update must be atomic.

=== Tables Involved ===
• wedding – locked using FOR UPDATE
• venue_booking
• photographer_booking
• band_booking
• registrar_booking

=== Full Transaction – SQL ===
{{{
BEGIN;

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

-- Calculate total cost across all services

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 ===
Prevents TOCTOU race conditions by locking the wedding row during validation.

== Savepoints – Partial Rollback ==
=== Concept ===
Savepoints allow partial rollback inside a transaction.

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

INSERT venue_booking;
INSERT photographer_booking;

SAVEPOINT after_core;

INSERT band_booking;

ROLLBACK TO SAVEPOINT after_core;

COMMIT;
}}}

== Isolation Levels ==
=== Overview ===
Isolation defines how concurrent transactions behave.

Problems prevented:

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

=== Levels ===

* READ COMMITTED – basic reads
* REPEATABLE READ – RSVP consistency
* SERIALIZABLE – full booking + budget validation

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

== Connection Pooling ==
=== Why needed ===
Pooling reuses DB connections instead of creating new ones per request.

=== Affected endpoints ===

* availability endpoints
* RSVP endpoints
* booking endpoints

=== 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
)
}}}

=== Parameters ===

* pool_size – base connections
* max_overflow – burst capacity
* pool_timeout – wait limit
* pool_recycle – prevents stale connections
* pool_pre_ping – checks connection health

== Conclusion ==
This phase introduced five full, schema-specific transactions for the Wedding Planner system:

* Full wedding booking with atomic service allocation
* Guest registration with RSVP seeding
* RSVP acceptance with attendance UPSERT
* Wedding cancellation with full cascade
* Budget validation with row locking

These ensure ACID compliance, prevent race conditions, and guarantee consistent multi-table operations in a concurrent environment.