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.

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.