= Security and Optimization =


== Assertions (CHECK Constraints) ==
To guarantee fundamental data validity at the lowest level, we utilize '''CHECK constraints'''. These declarative rules prevent invalid data from ever being written to the database, ensuring a baseline of data quality across the system.

||= Table =||= Constraint Name =||= Rule =||= Purpose =||
|| employees || employees_net_salary_nonneg || net_salary >= 0 (or NULL) || Prevents negative payroll values while allowing them to be unset. ||
|| employees || employees_gross_salary_nonneg || gross_salary >= 0 (or NULL) || Prevents negative payroll values while allowing them to be unset. ||
|| inventories || inventories_qty_nonneg || quantity >= 0 || Ensures that product stock levels cannot be negative. ||
|| inventories || inventories_restock_nonneg || restock_level >= 0 (or NULL) || Ensures that restock thresholds cannot be negative. ||
|| products || products_price_nonneg || price >= 0 || Disallows products from having negative prices. ||
|| order_items || order_items_qty_pos || quantity > 0 || Requires that every item added to an order has a positive quantity. ||
|| order_items || order_items_price_nonneg || price >= 0 || Disallows negative unit prices on ordered items. ||
|| payments || payments_amount_nonneg || amount >= 0 || Ensures all payment and tip amounts are non-negative. ||
|| payments || payments_tip_nonneg || tip_amount >= 0 || Ensures all payment and tip amounts are non-negative. ||
|| tables || tables_capacity_pos || seat_capacity > 0 || Requires that every restaurant table has a positive seating capacity. ||
|| reservations || reservations_people_pos || number_of_people > 0 || Ensures that a reservation is made for at least one person. ||
|| front_staff || front_staff_tip_pct || tip_percent BETWEEN 0 AND 100 (or NULL) || Confines the tip percentage to a valid range if specified. ||

== Index Performance Analysis ==
We conducted a thorough analysis of key indexes to validate their performance impact on critical query patterns. The following sections detail the results, comparing query plans using EXPLAIN (ANALYZE, BUFFERS) with and without each index.

=== Index: frontstaff_managed_reservations(table_number) — fmr_table_idx ===
This B-Tree index is designed to accelerate lookups on the frontstaff_managed_reservations table for a specific table_number. This is a frequent operation for front-of-house staff managing table assignments.

'''Query Analyzed''': SELECT * FROM frontstaff_managed_reservations WHERE table_number = 1;

'''Selectivity''': High (~0.1% of 200k rows)

||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
|| Without index || Sequential Scan || 16.235 ms || hit=32, read=1439 ||
|| '''With index''' || '''Bitmap Index Scan''' || '''0.297 ms''' || '''hit=204, read=3''' ||

'''Performance Impact''':
The index provides a ~54x speedup in execution time. More importantly, it reduces disk reads by over 450x (from 1439 to 3), transforming an inefficient full table scan into a highly targeted index lookup.

'''Conclusion''':
This index is essential and provides a massive performance gain for selective table lookups, a core workflow of the application. Retain.

=== GiST Index: reservations_span_expr_gist ===
This is a GiST expression index on a tsrange created from reservation start times and stay lengths. It is built to optimize time-based overlap queries, which are critical for preventing double bookings and checking table availability.

'''Query Analyzed''': tsrange(...) && tsrange(...) (checking for overlap in the next 30 minutes)

'''Selectivity''': Medium (~2.1% of 500k rows)

||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
|| Without index || Parallel Seq Scan || 141.415 ms || hit=94, read=4641 ||
|| '''With index''' || '''Bitmap Index Scan (GiST)''' || '''3.646 ms''' || '''hit=249, read=0''' ||

'''Performance Impact''':
The GiST index is purpose-built for this type of query and delivers a ~39x speedup. It completely eliminates disk reads for this query by efficiently handling the range overlap && operator.

'''Conclusion''':
This index is fundamental for enforcing time-overlap constraints efficiently. It is critical for availability checks, double-booking prevention, and time-window dashboards. Retain.

=== Index: payments(order_id) — idx_payments_order ===
A standard B-Tree index on the order_id foreign key column in the payments table. This is intended to speed up joins and direct lookups from an order to its associated payments.

'''Queries Analyzed''': COUNT(*) and JOIN on payments for a specific order_id.

'''Selectivity''': Extremely high (finding a few payments among ~700k rows)

||= Query Type =||= Condition =||= Plan Type =||= Execution Time =||
|| COUNT(*) || Without index || Parallel Seq Scan || 34.445 ms ||
|| COUNT(*) || '''With index''' || '''Index Only Scan''' || '''0.120 ms''' ||
|| JOIN || Without index || Parallel Seq Scan || 33.313 ms ||
|| JOIN || '''With index''' || '''Bitmap Index Scan''' || '''0.075 ms''' ||

'''Performance Impact''':
For highly selective equality lookups, the index offers a dramatic ~280-440x speedup. It allows the database to avoid scanning hundreds of thousands of payment rows by jumping directly to the relevant entries. The ability to perform an "Index Only Scan" for the count is especially efficient.

'''Conclusion''':
This index is indispensable for fast foreign key lookups and joins, a foundational operation for retrieving order and payment details. Retain.

=== Index: payments(created_at) — idx_payments_created_at ===
A time-series B-Tree index on the created_at timestamp in the payments table. Its value depends heavily on the selectivity of the time window being queried.

'''Queries Analyzed''': COUNT(*) on payments over a broad (90-day) and a narrow (1-minute) time window.

||= Time Window =||= Condition =||= Plan Type =||= Execution Time =||
|| 90 Days (Low Selectivity) || Without index || Parallel Seq Scan || 234.384 ms ||
|| 90 Days (Low Selectivity) || With index || Parallel Seq Scan || 239.074 ms ||
|| 1 Minute (High Selectivity) || Without index || Parallel Seq Scan || 119.739 ms ||
|| 1 Minute (High Selectivity) || '''With index''' || '''Index Only Scan''' || '''0.069 ms''' ||

'''Performance Impact''':
The analysis shows two distinct behaviors:

Broad Window (Low Selectivity): When the query selects a large fraction of the table (e.g., 90 days of data), the planner correctly determines that a full Parallel Seq Scan is more efficient. The index is ignored and offers no benefit.

Narrow Window (High Selectivity): When querying a tiny, recent time slice (e.g., the last minute), the index is overwhelmingly effective, providing a ~1,700x speedup and using an extremely fast Index Only Scan.

'''Conclusion''':
This index is highly valuable for "recent data" use cases like real-time dashboards or alerts. It is neutral for broad analytical queries where sequential scans are appropriate. The planner correctly chooses the best path in both scenarios. Retain.

== Triggered Business Rules ==
For more complex, state-dependent business logic that cannot be handled by simple CHECK constraints, we leverage '''triggers'''. These automatically execute procedural functions in response to specific data modification events (e.g., INSERT, UPDATE), thereby enforcing sophisticated application rules directly within the database.

=== TRIGGER: Prevent Overlapping Table Bookings ===

'''Objective''': To guarantee that a single restaurant table cannot be assigned to two different reservations that overlap in time.

'''Associated Function''': enforce_no_double_booking()

'''Events''': AFTER INSERT, AFTER UPDATE on the frontstaff_managed_reservations table. This is implemented as a CONSTRAINT trigger, which is DEFERRABLE INITIALLY DEFERRED.

'''Implementation Details''':
The trigger calculates the time interval of the incoming reservation using PostgreSQL's tsrange type, with a default stay length of 2 hours if not specified. The range is defined with inclusive start and exclusive end bounds ([)) to allow for back-to-back bookings. It then queries existing reservations for the same table to check for any temporal overlap using the && operator. If a conflict is detected, the transaction is rolled back with an exception.

Critically, the use of DEFERRABLE INITIALLY DEFERRED means the constraint is checked at the end of the transaction, not after each row modification. This is essential for correctly handling multi-row inserts or updates that might otherwise fail prematurely.

{{{
CREATE OR REPLACE FUNCTION enforce_no_double_booking()
RETURNS trigger AS $$
DECLARE
new_span tsrange;
conflict_exists boolean;
BEGIN
SELECT tsrange(
r.datetime,
r.datetime + (COALESCE(r.stay_length, 2) * INTERVAL '1 hour'),
'[)'
)
INTO new_span
FROM reservations r
WHERE r.id = NEW.reservation_id;

IF new_span IS NULL THEN
RAISE EXCEPTION 'Reservation % not found or invalid', NEW.reservation_id;
END IF;

SELECT EXISTS (
SELECT 1
FROM frontstaff_managed_reservations fmr
JOIN reservations r2 ON r2.id = fmr.reservation_id
WHERE fmr.table_number = NEW.table_number
AND (NEW.id IS NULL OR fmr.id <> NEW.id) -- Exclude self in UPDATE
AND tsrange(
r2.datetime,
r2.datetime + (COALESCE(r2.stay_length, 2) * INTERVAL '1 hour'),
'[)'
) && new_span
)
INTO conflict_exists;

IF conflict_exists THEN
RAISE EXCEPTION 'Double booking prevented: table % has overlapping reservations', NEW.table_number;
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;
}}}

'''Supporting Indexes for Performance''':

An index on frontstaff_managed_reservations(table_number) to rapidly locate all reservations for the target table.

A functional GiST index on the tsrange expression (reservations_span_expr_gist) to allow for highly efficient range overlap (&&) searches.

=== TRIGGER: Enforce Seating Capacity ===

'''Objective''': To prevent a reservation from being assigned to a table that has insufficient seating capacity for the party size.

'''Associated Function''': fmr_capacity_guard()

'''Events''': BEFORE INSERT, BEFORE UPDATE on frontstaff_managed_reservations.

'''Implementation Details''':
Before a row is written, this trigger fetches the seat_capacity of the target table and the number_of_people from the associated reservation. It verifies that the party size does not exceed the table's capacity. If it does, an exception is raised, preventing the invalid assignment.

{{{
CREATE OR REPLACE FUNCTION fmr_capacity_guard() RETURNS trigger AS $$
DECLARE
cap   int;
party bigint;
BEGIN
SELECT seat_capacity INTO cap FROM tables WHERE table_number = NEW.table_number;
SELECT number_of_people INTO party FROM reservations WHERE id = NEW.reservation_id;

IF cap IS NULL OR party IS NULL THEN
RAISE EXCEPTION 'Invalid reservation % or table %', NEW.reservation_id, NEW.table_number;
END IF;

IF party > cap THEN
RAISE EXCEPTION 'Party size % exceeds capacity % for table %', party, cap, NEW.table_number;
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;
}}}

'''Supporting Indexes for Performance''':

No special indexes are needed. The trigger's lookups are highly efficient as they operate on the primary keys of the tables and reservations tables.

=== TRIGGER: Update Order Status Upon Payment ===

'''Objective''': To automate the business process of marking an order as 'PAID' as soon as a payment is recorded for it.

'''Associated Function''': payments_mark_order_paid()

'''Events''': AFTER INSERT on the payments table.

'''Implementation Details''':
Immediately after a new record is successfully inserted into the payments table, this trigger fires an UPDATE statement on the orders table. It sets the status of the corresponding order (identified by NEW.order_id) to 'PAID', ensuring the system state remains consistent.

{{{
CREATE OR REPLACE FUNCTION payments_mark_order_paid() RETURNS trigger AS $$
BEGIN
UPDATE orders
SET status = 'PAID'
WHERE id = NEW.order_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
}}}

'''Supporting Indexes for Performance''':

The UPDATE operation is optimized by the primary key index on orders(id).

'''Future Considerations''':

This implementation assumes that any single payment settles the full order amount. For a system supporting partial payments, this logic would need to be enhanced to check if the sum of payments for an order meets or exceeds the order's total before changing the status.

== MATERIALIZED VIEW: Pre-Aggregated Daily Sales Analytics ==
To support high-frequency analytical queries without imposing a heavy real-time aggregation load on the core transactional tables, we employ a '''materialized view'''. This view, mv_payments_daily_channel, pre-computes daily revenue, tips, and order counts, segmented by the sales channel (TAB or ONLINE).

{{{
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_payments_daily_channel AS
WITH orders_channel AS (
SELECT
o.id AS order_id,
CASE
WHEN EXISTS (SELECT 1 FROM tab_orders t WHERE t.order_id = o.id) THEN 'TAB'
WHEN EXISTS (SELECT 1 FROM online_orders oo WHERE oo.order_id = o.id) THEN 'ONLINE'
ELSE 'UNKNOWN'
END AS channel
FROM orders o
)
SELECT
(date_trunc('day', p.created_at))::date AS day,
oc.channel,
COUNT(DISTINCT p.order_id)            AS paid_orders_cnt,
SUM(p.amount)::numeric(14,2)          AS revenue,
SUM(p.tip_amount)::numeric(14,2)      AS tip_total
FROM payments p
JOIN orders_channel oc ON oc.order_id = p.order_id
GROUP BY (date_trunc('day', p.created_at))::date, oc.channel;
}}}

'''Indexing Strategy''':

A UNIQUE index on (day, channel) (ux_mv_payments_daily_channel) ensures data integrity within the view and is a prerequisite for enabling concurrent refreshes (REFRESH MATERIALIZED VIEW CONCURRENTLY).

Indexes on payments(order_id) and payments(created_at) are crucial for efficiently building and refreshing the materialized view's data from the underlying base tables.