= 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.

=== Flask Implementation ===
{{{
from sqlalchemy.exc import SQLAlchemyError
from datetime import date

def accept_rsvp_and_assign_seat(guest_id, event_id, table_number, role='Guest'):
try:
db.session.begin()

    # STEP 1: Find RSVP record
    rsvp = db.session.query(EventRSVP).filter_by(
        guest_id=guest_id,
        event_id=event_id
    ).first()

    # RSVP must exist
    if not rsvp:
        raise ValueError('RSVP record not found')

    # STEP 2: Update RSVP status
    rsvp.status = 'accepted'
    rsvp.response_date = date.today()

    # STEP 3: Check if attendance already exists
    attendance = db.session.query(Attendance).filter_by(
        guest_id=guest_id,
        event_id=event_id
    ).first()

    # STEP 4: UPSERT logic
    if attendance:
        attendance.status = 'attending'
        attendance.table_number = table_number
        attendance.role = role
    else:
        attendance = Attendance(
            status='attending',
            table_number=table_number,
            role=role,
            guest_id=guest_id,
            event_id=event_id
        )
        db.session.add(attendance)

    db.session.commit()

    return {
        'status': 'success',
        'guest_id': guest_id,
        'event_id': event_id,
        'table_number': table_number
    }

except (ValueError, SQLAlchemyError) as e:
    db.session.rollback()

    return {
        'status': 'error',
        'reason': str(e)
    }, 409

}}}

=== Explanation ===
This transaction guarantees that RSVP acceptance and attendance assignment happen together atomically. If attendance creation fails after the RSVP update, the rollback restores the RSVP to its previous state, preventing inconsistencies.

== 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.

=== Flask Implementation ===
{{{
from sqlalchemy.exc import SQLAlchemyError

def cancel_wedding(wedding_id):
try:
db.session.begin()

    # STEP 1: Find wedding
    wedding = db.session.query(Wedding).filter_by(
        wedding_id=wedding_id
    ).first()

    if not wedding:
        raise ValueError('Wedding not found')

    # STEP 2: Update wedding status
    wedding.notes = 'Wedding cancelled'

    # STEP 3: Cancel venue bookings
    db.session.query(VenueBooking).filter_by(
        wedding_id=wedding_id
    ).update({
        'status': 'cancelled'
    })

    # STEP 4: Cancel photographer bookings
    db.session.query(PhotographerBooking).filter_by(
        wedding_id=wedding_id
    ).update({
        'status': 'cancelled'
    })

    # STEP 5: Cancel band bookings
    db.session.query(BandBooking).filter_by(
        wedding_id=wedding_id
    ).update({
        'status': 'cancelled'
    })

    # STEP 6: Cancel registrar bookings
    db.session.query(RegistrarBooking).filter_by(
        wedding_id=wedding_id
    ).update({
        'status': 'cancelled'
    })

    # STEP 7: Cancel events
    db.session.query(Event).filter_by(
        wedding_id=wedding_id
    ).update({
        'status': 'cancelled'
    })

    # STEP 8: Find all wedding events
    events = db.session.query(Event.event_id).filter_by(
        wedding_id=wedding_id
    ).all()

    event_ids = [e.event_id for e in events]

    # STEP 9: Mark attendance absent
    if event_ids:
        db.session.query(Attendance).filter(
            Attendance.event_id.in_(event_ids)
        ).update({
            'status': 'absent'
        }, synchronize_session=False)

    db.session.commit()

    return {
        'status': 'success',
        'wedding_id': wedding_id
    }

except (ValueError, SQLAlchemyError) as e:
    db.session.rollback()

    return {
        'status': 'error',
        'reason': str(e)
    }, 500

}}}

=== Explanation ===
This transaction performs a full cancellation cascade across all dependent tables. Every update occurs inside one transaction so the database can never contain partially cancelled wedding data.


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

=== Flask Implementation ===
{{{
from sqlalchemy.exc import SQLAlchemyError

def validate_wedding_budget(wedding_id):
try:
db.session.begin()

    # STEP 1: Lock wedding row
    wedding = db.session.query(Wedding)\
        .filter_by(wedding_id=wedding_id)\
        .with_for_update()\
        .first()

    if not wedding:
        raise ValueError('Wedding not found')

    budget = wedding.budget

    # STEP 2: Calculate venue costs
    venue_total = db.session.query(
        db.func.coalesce(db.func.sum(VenueBooking.price), 0)
    ).filter_by(
        wedding_id=wedding_id
    ).scalar()

    # STEP 3: Calculate photographer costs
    photographer_total = db.session.query(
        db.func.coalesce(db.func.sum(PhotographerBooking.price), 0)
    ).filter_by(
        wedding_id=wedding_id
    ).scalar()

    # STEP 4: Calculate band costs
    band_total = db.session.query(
        db.func.coalesce(db.func.sum(BandBooking.price), 0)
    ).filter_by(
        wedding_id=wedding_id
    ).scalar()

    # STEP 5: Calculate registrar costs
    registrar_total = db.session.query(
        db.func.coalesce(db.func.sum(RegistrarBooking.price), 0)
    ).filter_by(
        wedding_id=wedding_id
    ).scalar()

    # STEP 6: Compute total cost
    total_cost = (
        venue_total +
        photographer_total +
        band_total +
        registrar_total
    )

    # STEP 7: Validate budget
    if total_cost > budget:
        raise ValueError(
            f'Budget exceeded. Total: {total_cost}, Budget: {budget}'
        )

    # STEP 8: Finalise wedding
    wedding.notes = 'Budget validated – wedding finalised'

    db.session.commit()

    return {
        'status': 'success',
        'wedding_id': wedding_id,
        'budget': float(budget),
        'total_cost': float(total_cost)
    }

except (ValueError, SQLAlchemyError) as e:
    db.session.rollback()

    return {
        'status': 'error',
        'reason': str(e)
    }, 409

}}}

=== Explanation ===
The with_for_update() row lock prevents concurrent transactions from modifying the wedding budget or related financial data during validation. This avoids TOCTOU (Time Of Check To Time Of Use) race conditions.

== 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.

=== Flask Implementation ===
{{{
from sqlalchemy.exc import SQLAlchemyError

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

    # STEP 1: Insert venue booking
    venue = VenueBooking(
        **venue_cfg,
        wedding_id=wedding_id
    )
    db.session.add(venue)

    # STEP 2: Insert photographer booking
    photographer = PhotographerBooking(
        **photo_cfg,
        wedding_id=wedding_id
    )
    db.session.add(photographer)

    # STEP 3: Create savepoint
    savepoint = db.session.begin_nested()

    try:
        # STEP 4: Attempt risky band booking
        band = BandBooking(
            **band_cfg,
            wedding_id=wedding_id
        )

        db.session.add(band)
        db.session.flush()

    except SQLAlchemyError:
        # STEP 5: Roll back only to savepoint
        savepoint.rollback()

    # STEP 6: Continue transaction
    db.session.commit()

    return {
        'status': 'partial_success'
    }

except SQLAlchemyError as e:
    db.session.rollback()

    return {
        'status': 'error',
        'reason': str(e)
    }, 500

}}}

=== Explanation ===
begin_nested() creates a database savepoint. If the band booking fails, only the operations after the savepoint are rolled back while earlier successful inserts remain intact.

=== 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.