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.