Changes between Initial Version and Version 1 of Security&Optimization

Timestamp:

09/04/25 23:23:38 (4 days ago)

Author:

221128

Comment:

--

Security&Optimization

@@ +1 @@
+
+= 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.