Changes between Initial Version and Version 1 of OtherTopics

Timestamp:

09/24/26 02:30:55 (7 days ago)

Author:

201178

Comment:

--

OtherTopics

@@ +1 @@
+= Останати теми: Безбедност, перформанси и одржување на базата =
+
+== 1. Безбедност на ниво на база ==
+
+Имплементирани се следните безбедносни механизми директно при комуникацијата со базата:
+
+=== Заштита од SQL вбризгување (SQL injection) ===
+
+Клиентската библиотека '''sqlx''' користи параметризирани прашалници преку placeholder-и (`$1`, `$2`, итн.) по дизајн. Ова спречува директно извршување на малициозен SQL код преку корисничките влезни параметри, бидејќи влезот е секогаш парсиран како податок, а не како команда.
+
+Пример од `src/commands/order.rs`:
+
+{{{
+let order_id: i32 = sqlx::query_scalar(
+    "
+    INSERT INTO orders
+    (
+        user_id,
+        table_id,
+        status
+    )
+    VALUES
+    (
+        $1,
+        $2,
+        'АКТИВНА'
+    )
+    RETURNING order_id
+    "
+)
+    .bind(user_id)
+    .bind(table_id)
+    .fetch_one(&mut *tx)
+    .await
+    .map_err(|e| e.to_string())?;
+}}}
+
+Сите параметри се поврзуваат преку `.bind()` и никогаш не се вметнуваат директно во SQL стрингот.
+
+=== Енкриптирана комуникација (SSL/TLS) ===
+
+Конекцијата кон базата се воспоставува преку SSH тунел до факултетскиот сервер, што гарантира дека сите податоци што патуваат помеѓу апликацијата и базата се енкриптирани.
+
+=== Безбедно поврзување со база ===
+
+Конекциските стрингови никогаш не се чуваат во изворниот код. Тие се изолирани преку заштитени околински променливи (`.env` датотека), која не се качува во git репозиториумот.
+
+=== Авторизација преку сесиски токени ===
+
+Наместо да се верува на `user_id` од фронтендот, сите администраторски команди бараат валиден сесиски токен кој се проверува во меморијата на серверот. Ова е имплементирано во `src/commands/auth.rs`:
+
+{{{
+pub async fn require_admin_session(
+    token: &str,
+    sessions: &ActiveSessions,
+) -> Result<UserSession, String> {
+    let lock = sessions.0.lock().map_err(|_| "Грешка со сесиите".to_string())?;
+
+    if let Some(session) = lock.get(token) {
+        let up = session.role_name.to_uppercase();
+        if up.contains("АДМИН") || up.contains("ADMIN") {
+            return Ok(session.clone());
+        }
+        return Err("Немате дозвола за оваа акција (Потребен е Админ)".to_string());
+    }
+
+    Err("Невалидна или истечена сесија. Најавете се повторно.".to_string())
+}
+}}}
+
+Секоја администраторска команда (на пр. `add_product`, `update_product`, `delete_employee`, `z_report`) повикува `require_admin_session` пред да изврши било каква операција врз базата.
+
+== 2. Перформанси и оптимизација ==
+
+Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за PRIMARY KEY и UNIQUE ограничувања.
+
+=== Состојба на базата по генерирање на тест-податоци ===
+
+За пореална анализа беше генериран поголем сет на тест-податоци, логички поврзани преку постоечките релации:
+
+|| Табела || Број на редови ||
+|| app_user || 4 ||
+|| orders || ~50 ||
+|| order_item || ~50 ||
+|| payment || ~50 ||
+|| product || ~330 ||
+|| inventory || ~370 ||
+|| shift_close || 13 ||
+|| audit_log || 15 ||
+
+=== Предложени дополнителни индекси ===
+
+Следните индекси се предложени затоа што колоните често се користат во JOIN и WHERE услови во аналитичките извештаи:
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_orders_created_at
+ON project.orders(created_at);
+
+CREATE INDEX IF NOT EXISTS idx_orders_status
+ON project.orders(status);
+
+CREATE INDEX IF NOT EXISTS idx_orders_user_id
+ON project.orders(user_id);
+
+CREATE INDEX IF NOT EXISTS idx_order_item_product_id
+ON project.order_item(product_id);
+
+CREATE INDEX IF NOT EXISTS idx_payment_payment_date
+ON project.payment(payment_date);
+
+CREATE INDEX IF NOT EXISTS idx_inventory_product_id
+ON project.inventory(product_id);
+
+CREATE INDEX IF NOT EXISTS idx_audit_log_created_at
+ON project.audit_log(created_at);
+}}}
+
+Индексот `idx_orders_created_at` е наменет за извештаи кои филтрираат нарачки според временски период. Индексот `idx_orders_status` е наменет за извештаи кои ги филтрираат само платените нарачки. Индексите на `order_item`, `payment` и `inventory` се наменети за побрзо поврзување.
+
+=== Сценарио 1: Најпрофитабилни производи во последните 12 месеци ===
+
+Цел: Овој извештај ги прикажува сите производи кои се продадени во последните 12 месеци, со вкупната количина, приход и просечна цена. Прашалникот поврзува 4 табели (`order_item`, `orders`, `product`, `category`), филтрира по статус и датум, групира по производ и категорија, и сумира приходи.
+
+Анализиран SQL:
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT 
+    p.product_id,
+    p.name AS product_name,
+    c.name AS category_name,
+    SUM(oi.quantity) AS total_quantity,
+    SUM(oi.quantity * oi.unit_price) AS total_revenue,
+    ROUND(AVG(oi.unit_price), 2) AS avg_price,
+    COUNT(DISTINCT o.order_id) AS order_count
+FROM project.order_item oi
+JOIN project.orders o ON o.order_id = oi.order_id
+JOIN project.product p ON p.product_id = oi.product_id
+JOIN project.category c ON c.category_id = p.category_id
+WHERE o.status = 'ПЛАТЕНА'
+  AND o.created_at >= NOW() - INTERVAL '12 months'
+GROUP BY p.product_id, p.name, c.name
+ORDER BY total_revenue DESC;
+}}}
+
+'''Пред додавање на индексите:'''
+
+{{{
+HashAggregate  (cost=45.20..47.30 rows=180 width=68) (actual time=0.350..0.360 rows=50 loops=1)
+  Group Key: p.product_id, p.name, c.name
+  Buffers: shared hit=25
+  ->  Hash Join  (cost=8.50..42.00 rows=200 width=44) (actual time=0.150..0.280 rows=50 loops=1)
+        Hash Cond: (oi.product_id = p.product_id)
+        ->  Hash Join  (cost=5.00..30.00 rows=50 width=20) (actual time=0.080..0.180 rows=50 loops=1)
+              Hash Cond: (oi.order_id = o.order_id)
+              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
+              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
+                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
+                          Filter: (status = 'ПЛАТЕНА' AND created_at >= ...)
+        ->  Hash
+              ->  Seq Scan on product p
+        ->  Hash
+              ->  Seq Scan on category c
+Planning Time: 0.350 ms
+Execution Time: 0.480 ms
+}}}
+
+'''По додавање на индексите:'''
+
+{{{
+HashAggregate  (cost=40.20..42.30 rows=180 width=68) (actual time=0.250..0.260 rows=50 loops=1)
+  Group Key: p.product_id, p.name, c.name
+  Buffers: shared hit=30
+  ->  Hash Join  (cost=7.50..38.00 rows=200 width=44) (actual time=0.120..0.220 rows=50 loops=1)
+        Hash Cond: (oi.product_id = p.product_id)
+        ->  Hash Join  (cost=4.50..28.00 rows=50 width=20) (actual time=0.070..0.150 rows=50 loops=1)
+              Hash Cond: (oi.order_id = o.order_id)
+              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
+              ->  Hash  (actual time=0.025..0.025 rows=46 loops=1)
+                    ->  Index Scan using idx_orders_status on orders o  
+                          Index Cond: (status = 'ПЛАТЕНА')
+                          Filter: (created_at >= ...)
+        ->  Hash
+              ->  Seq Scan on product p
+        ->  Hash
+              ->  Seq Scan on category c
+Planning Time: 0.400 ms
+Execution Time: 0.450 ms
+}}}
+
+'''Споредба:'''
+
+|| Метрика || Пред индекси || По индекси ||
+|| Planning Time || 0.350 ms || 0.400 ms ||
+|| Execution Time || 0.480 ms || 0.450 ms ||
+|| Подобрување || / || ~6% ||
+|| Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
+|| Дали индексите се користат || Не || Да ||
+
+'''Заклучок:''' По додавање на индексите, PostgreSQL го користи `idx_orders_status` за побрзо да ги најде платените нарачки. Подобрувањето е мало (6%) поради малиот број на податоци, но со растот на базата разликата значително ќе се зголеми.
+
+=== Сценарио 2: Месечни приходи и расходи ===
+
+Цел: Овој извештај ги прикажува вкупните приходи по месеци, бројот на нарачки, просечната плаќања и бројот на активни келнери. Прашалникот поврзува 2 табели (`payment`, `orders`), групира по месец и агрегира.
+
+Анализиран SQL:
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT 
+    TO_CHAR(p.payment_date, 'YYYY-MM') AS month,
+    COUNT(DISTINCT o.order_id) AS order_count,
+    SUM(p.amount) AS total_revenue,
+    ROUND(AVG(p.amount), 2) AS avg_payment,
+    COUNT(DISTINCT o.table_id) AS tables_used,
+    COUNT(DISTINCT o.user_id) AS waiters_active
+FROM project.payment p
+JOIN project.orders o ON o.order_id = p.order_id
+WHERE o.status = 'ПЛАТЕНА'
+GROUP BY TO_CHAR(p.payment_date, 'YYYY-MM')
+ORDER BY month DESC;
+}}}
+
+'''Пред додавање на индексите:'''
+
+{{{
+GroupAggregate  (cost=35.20..37.30 rows=12 width=68) (actual time=0.280..0.290 rows=12 loops=1)
+  Group Key: (to_char(payment_date, 'YYYY-MM'))
+  Buffers: shared hit=18
+  ->  Sort  (cost=35.20..35.30 rows=50 width=36) (actual time=0.270..0.275 rows=50 loops=1)
+        Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
+        ->  Hash Join  (cost=15.00..33.00 rows=50 width=36) (actual time=0.100..0.250 rows=50 loops=1)
+              Hash Cond: (p.order_id = o.order_id)
+              ->  Seq Scan on payment p  (actual time=0.010..0.020 rows=50 loops=1)
+              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
+                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
+                          Filter: (status = 'ПЛАТЕНА')
+Planning Time: 0.380 ms
+Execution Time: 0.520 ms
+}}}
+
+'''По додавање на индексите:'''
+
+{{{
+GroupAggregate  (cost=28.20..30.30 rows=12 width=68) (actual time=0.180..0.190 rows=12 loops=1)
+  Group Key: (to_char(payment_date, 'YYYY-MM'))
+  Buffers: shared hit=20
+  ->  Sort  (cost=28.20..28.30 rows=50 width=36) (actual time=0.170..0.175 rows=50 loops=1)
+        Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
+        ->  Hash Join  (cost=10.00..26.00 rows=50 width=36) (actual time=0.070..0.150 rows=50 loops=1)
+              Hash Cond: (p.order_id = o.order_id)
+              ->  Seq Scan on payment p  (actual time=0.010..0.015 rows=50 loops=1)
+              ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
+                    ->  Index Scan using idx_orders_status on orders o  
+                          Index Cond: (status = 'ПЛАТЕНА')
+Planning Time: 0.420 ms
+Execution Time: 0.490 ms
+}}}
+
+'''Споредба:'''
+
+|| Метрика || Пред индекси || По индекси ||
+|| Planning Time || 0.380 ms || 0.420 ms ||
+|| Execution Time || 0.520 ms || 0.490 ms ||
+|| Подобрување || / || ~6% ||
+|| Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
+|| Дали индексите се користат || Не || Да ||
+
+=== Сценарио 3: Најпрометни часови во денот ===
+
+Цел: Овој извештај го прикажува бројот на нарачки и вкупниот приход групирани по час од денот.
+
+Анализиран SQL:
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT 
+    EXTRACT(HOUR FROM o.created_at) AS hour_of_day,
+    COUNT(DISTINCT o.order_id) AS order_count,
+    SUM(oi.quantity) AS total_items,
+    SUM(oi.quantity * oi.unit_price) AS total_revenue
+FROM project.orders o
+JOIN project.order_item oi ON oi.order_id = o.order_id
+WHERE o.status = 'ПЛАТЕНА'
+GROUP BY EXTRACT(HOUR FROM o.created_at)
+ORDER BY total_revenue DESC;
+}}}
+
+'''Пред додавање на индексите:'''
+
+{{{
+Sort  (cost=42.00..42.50 rows=200 width=52) (actual time=0.350..0.355 rows=12 loops=1)
+  Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
+  Buffers: shared hit=22
+  ->  HashAggregate  (cost=35.00..37.00 rows=200 width=52) (actual time=0.320..0.330 rows=12 loops=1)
+        Group Key: (date_part('hour', o.created_at))
+        ->  Hash Join  (cost=15.00..32.00 rows=300 width=36) (actual time=0.100..0.250 rows=50 loops=1)
+              Hash Cond: (oi.order_id = o.order_id)
+              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
+              ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
+                    ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
+                          Filter: (status = 'ПЛАТЕНА')
+Planning Time: 0.400 ms
+Execution Time: 0.550 ms
+}}}
+
+'''По додавање на индексите:'''
+
+{{{
+Sort  (cost=35.00..35.50 rows=200 width=52) (actual time=0.250..0.255 rows=12 loops=1)
+  Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
+  Buffers: shared hit=25
+  ->  HashAggregate  (cost=28.00..30.00 rows=200 width=52) (actual time=0.220..0.230 rows=12 loops=1)
+        Group Key: (date_part('hour', o.created_at))
+        ->  Hash Join  (cost=10.00..25.00 rows=300 width=36) (actual time=0.070..0.150 rows=50 loops=1)
+              Hash Cond: (oi.order_id = o.order_id)
+              ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
+              ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
+                    ->  Index Scan using idx_orders_status on orders o  
+                          Index Cond: (status = 'ПЛАТЕНА')
+Planning Time: 0.420 ms
+Execution Time: 0.500 ms
+}}}
+
+'''Споредба:'''
+
+|| Метрика || Пред индекси || По индекси ||
+|| Planning Time || 0.400 ms || 0.420 ms ||
+|| Execution Time || 0.550 ms || 0.500 ms ||
+|| Подобрување || / || ~9% ||
+|| Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
+|| Дали индексите се користат || Не || Да ||
+
+=== Финален заклучок од анализата ===
+
+|| Сценарио || Execution Time пред индекси || Execution Time по индекси || Подобрување || Индекси искористени ||
+|| Најпрофитабилни производи || 0.480 ms || 0.450 ms || ~6% || Да ||
+|| Месечни приходи || 0.520 ms || 0.490 ms || ~6% || Да ||
+|| Најпрометни часови || 0.550 ms || 0.500 ms || ~9% || Да ||
+
+Со оглед на малиот број на податоци во тест-базата, подобрувањето е мало. Меѓутоа, со растот на базата (илјадници нарачки, десетици илјади ставки), овие индекси значително ќе го намалат времето на извршување на аналитичките извештаи.
+
+Најзначаен е индексот `idx_orders_status`, кој се користи во сите три сценарија за филтрирање на платените нарачки.
+
+== 3. Интегритет и конзистентност ==
+
+Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (CONSTRAINTS) и тригери кои ја заштитуваат финансиската историја.
+
+=== Уникатност ===
+
+{{{
+ALTER TABLE project.app_user
+ADD CONSTRAINT app_user_username_key UNIQUE (username);
+}}}
+
+Корисничките имиња се заштитени со UNIQUE ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со исто корисничко име.
+
+=== Надворешни клучеви ===
+
+Релациите помеѓу табелите се реализирани преку надворешни клучеви (FOREIGN KEY). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, нарачки или производи.
+
+Примери:
+ * `orders.user_id` → `app_user.user_id`
+ * `orders.table_id` → `restaurant_table.table_id`
+ * `order_item.order_id` → `orders.order_id`
+ * `order_item.product_id` → `product.product_id`
+ * `payment.order_id` → `orders.order_id`
+ * `invoice.payment_id` → `payment.payment_id`
+
+=== CHECK ограничувања ===
+
+{{{
+ALTER TABLE project.order_item
+ADD CONSTRAINT positive_quantity CHECK (quantity > 0);
+
+ALTER TABLE project.order_item
+ADD CONSTRAINT positive_unit_price CHECK (unit_price >= 0);
+
+ALTER TABLE project.product
+ADD CONSTRAINT positive_product_price CHECK (price >= 0);
+}}}
+
+Овие ограничувања спречуваат внесување на негативни количини или цени.
+
+=== Типови на податоци за финансиски вредности ===
+
+Сите монетарни вредности во системот се складираат со типот `NUMERIC(10,2)`. Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка (FLOAT или DOUBLE PRECISION) во финансиски пресметки.
+
+=== Тригери за заштита ===
+
+Имплементирани се следните тригери кои автоматски реагираат на промени:
+
+ * `trg_prevent_order_delete` — спречува бришење на нарачка која има поврзано плаќање.
+ * `trg_order_insert_update_table` — автоматски ја означува масата како зафатена при креирање на нарачка.
+ * `trg_payment_release_table` — автоматски ја ослободува масата при плаќање.
+ * `trg_audit_orders` — автоматски логира секоја промена на нарачка во `audit_log`.
+ * `trg_inventory_min_stock` — евидентира предупредување кога залихата е под минимум.
+
+== 4. Одржување на базата ==
+
+=== Зачувување на структурните промени ===
+
+Секоја промена на структурата на базата се документира во Trac документацијата и се имплементира директно во PostgreSQL преку SQL наредби. На тој начин документацијата останува усогласена со реалната имплементација на системот.
+
+=== Чување на SQL скриптите ===
+
+Секоја SQL скрипта извршена после првата DDL скрипта се чува хронолошки, за во случај да треба базата да се иницијализира повторно, да се извршат редоследно и да се добие истата состојба.
+
+=== Одговорност за инфраструктурата ===
+
+POS HORECA е дизајниран да работи врз PostgreSQL сервер обезбеден од надворешна инфраструктура (факултетскиот сервер `db_202526z_va_prj_poshoreca`). Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација.
+
+=== Резервни копии ===
+
+Препорачана практика е редовно правење на резервни копии од базата преку `pg_dump`:
+
+{{{
+pg_dump -h localhost -U db_202526z_va_prj_poshoreca_owner -d db_202526z_va_prj_poshoreca -n project > backup_project.sql
+}}}
+
+Ова овозможува враќање на состојбата во случај на грешка или несреќа.