= Останати теми: Безбедност, перформанси и одржување на базата =

== 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 || 10.088 ||
|| order_item || 30.048 ||
|| payment || 5.046 ||
|| 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;
}}}

Резултат од EXPLAIN (ANALYZE, BUFFERS):

{{{
Sort  (cost=916.67..916.77 rows=40 width=329) (actual time=110.670..110.675 rows=24 loops=1)
  Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
  Sort Method: quicksort  Memory: 27kB
  Buffers: shared hit=42572
  ->  GroupAggregate  (cost=914.00..915.60 rows=40 width=329) (actual time=98.576..110.651 rows=24 loops=1)
        Group Key: p.product_id, c.name
        Buffers: shared hit=42572
        ->  Sort  (cost=914.00..914.10 rows=40 width=273) (actual time=98.557..99.821 rows=20940 loops=1)
              Sort Key: p.product_id, c.name, o.order_id
              Sort Method: quicksort  Memory: 2078kB
              Buffers: shared hit=42572
              ->  Nested Loop  (cost=97.89..912.94 rows=40 width=273) (actual time=5.056..56.400 rows=20940 loops=1)
                    Buffers: shared hit=42572
                    ->  Nested Loop  (cost=97.74..908.87 rows=40 width=59) (actual time=5.043..46.378 rows=20940 loops=1)
                          Buffers: shared hit=42562
                          ->  Hash Join  (cost=97.60..902.23 rows=40 width=28) (actual time=5.030..17.485 rows=20940 loops=1)
                                Hash Cond: (oi.order_id = o.order_id)
                                Buffers: shared hit=682
                                ->  Seq Scan on order_item oi  (cost=0.00..741.48 rows=24048 width=28) (actual time=0.019..4.361 rows=30048 loops=1)
                                      Buffers: shared hit=501
                                ->  Hash  (cost=97.43..97.43 rows=13 width=4) (actual time=4.999..5.000 rows=7010 loops=1)
                                      Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 311kB
                                      Buffers: shared hit=181
                                      ->  Bitmap Heap Scan on orders o  (cost=4.58..97.43 rows=13 width=4) (actual time=0.485..3.863 rows=7010 loops=1)
                                            Recheck Cond: ((status)::text = 'ПЛАТЕНА'::text)
                                            Filter: (created_at >= (now() - '1 year'::interval))
                                            Heap Blocks: exact=168
                                            Buffers: shared hit=181
                                            ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..4.58 rows=39 width=0) (actual time=0.443..0.443 rows=13992 loops=1)
                                                  Index Cond: ((status)::text = 'ПЛАТЕНА'::text)
                                                  Buffers: shared hit=13
                          ->  Index Scan using product_pkey on product p  (cost=0.15..0.17 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=20940)
                                Index Cond: (product_id = oi.product_id)
                                Buffers: shared hit=41880
                    ->  Memoize  (cost=0.15..0.20 rows=1 width=222) (actual time=0.000..0.000 rows=1 loops=20940)
                          Cache Key: p.category_id
                          Cache Mode: logical
                          Hits: 20935  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                          Buffers: shared hit=10
                          ->  Index Scan using category_pkey on category c  (cost=0.14..0.19 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=5)
                                Index Cond: (category_id = p.category_id)
                                Buffers: shared hit=10
Planning:
  Buffers: shared hit=11
Planning Time: 0.559 ms
Execution Time: 110.770 ms
}}}

'''Споредба:'''

|| Метрика || Вредност ||
|| Planning Time || 0.559 ms ||
|| Execution Time || 110.770 ms ||
|| Вкупно вратени редови || 24 ||
|| Тип на скенирање на orders || '''Bitmap Index Scan (idx_orders_status)''' ||
|| Индекси искористени || idx_orders_status, product_pkey, category_pkey ||
|| Memoize оптимизација || Да (20.935 hits, 5 misses) ||

'''Заклучок:''' Со растот на базата од 46 на 10.000+ нарачки, PostgreSQL почна да го користи индексот `idx_orders_status` преку `Bitmap Index Scan` за филтрирање на платените нарачки, наместо Seq Scan. Дополнително, PostgreSQL користи `Memoize` оптимизација за категориите, намалувајќи 20.935 повторени читања на само 5.

=== Сценарио 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;
}}}

Резултат од EXPLAIN (ANALYZE, BUFFERS):

{{{
GroupAggregate  (cost=681.74..804.98 rows=3521 width=120) (actual time=15.977..18.246 rows=13 loops=1)
  Group Key: (to_char(p.payment_date, 'YYYY-MM'::text))
  Buffers: shared hit=208
  ->  Sort  (cost=681.74..690.55 rows=3521 width=50) (actual time=15.791..16.167 rows=5046 loops=1)
        Sort Key: (to_char(p.payment_date, 'YYYY-MM'::text)) DESC, o.order_id
        Sort Method: quicksort  Memory: 429kB
        Buffers: shared hit=208
        ->  Hash Join  (cost=153.54..474.32 rows=3521 width=50) (actual time=1.766..8.294 rows=5046 loops=1)
              Hash Cond: (o.order_id = p.order_id)
              Buffers: shared hit=208
              ->  Seq Scan on orders o  (cost=0.00..293.57 rows=7010 width=12) (actual time=0.018..2.539 rows=7010 loops=1)
                    Filter: ((status)::text = 'ПЛАТЕНА'::text)
                    Rows Removed by Filter: 3036
                    Buffers: shared hit=168
              ->  Hash  (cost=90.46..90.46 rows=5046 width=18) (actual time=1.719..1.720 rows=5046 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 321kB
                    Buffers: shared hit=40
                    ->  Seq Scan on payment p  (cost=0.00..90.46 rows=5046 width=18) (actual time=0.007..0.750 rows=5046 loops=1)
                          Buffers: shared hit=40
Planning:
  Buffers: shared hit=93
Planning Time: 0.793 ms
Execution Time: 18.309 ms
}}}

'''Споредба:'''

|| Метрика || Вредност ||
|| Planning Time || 0.793 ms ||
|| Execution Time || 18.309 ms ||
|| Вкупно вратени редови || 13 (месеци) ||
|| Тип на скенирање на orders || Seq Scan (со Filter на status) ||
|| Индекси искористени || (не се користат поради мал број на вратени редови) ||

'''Заклучок:''' Овој извештај враќа само 13 редови (месеци) и PostgreSQL избира Seq Scan на двете табели, бидејќи агрегацијата бара читање на сите 5.046 плаќања. Индексот `idx_payment_payment_date` не се користи бидејќи нема филтер по датум (сите плаќања се во опсегот).

=== Сценарио 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;
}}}

Резултат од EXPLAIN (ANALYZE, BUFFERS):

{{{
Sort  (cost=3738.84..3756.37 rows=7010 width=80) (actual time=61.237..61.241 rows=24 loops=1)
  Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
  Sort Method: quicksort  Memory: 26kB
  Buffers: shared hit=669
  ->  GroupAggregate  (cost=2819.00..3291.07 rows=7010 width=80) (actual time=50.305..61.219 rows=24 loops=1)
        Group Key: (EXTRACT(hour FROM o.created_at))
        Buffers: shared hit=669
        ->  Sort  (cost=2819.00..2871.42 rows=20967 width=45) (actual time=49.774..51.274 rows=20940 loops=1)
              Sort Key: (EXTRACT(hour FROM o.created_at)), o.order_id
              Sort Method: quicksort  Memory: 1586kB
              Buffers: shared hit=669
              ->  Hash Join  (cost=381.20..1314.01 rows=20967 width=45) (actual time=3.765..20.296 rows=20940 loops=1)
                    Hash Cond: (oi.order_id = o.order_id)
                    Buffers: shared hit=669
                    ->  Seq Scan on order_item oi  (cost=0.00..801.48 rows=30048 width=13) (actual time=0.014..2.618 rows=30048 loops=1)
                          Buffers: shared hit=501
                    ->  Hash  (cost=293.57..293.57 rows=7010 width=12) (actual time=3.724..3.725 rows=7010 loops=1)
                          Buckets: 8192  Batches: 1  Memory Usage: 366kB
                          Buffers: shared hit=168
                          ->  Seq Scan on orders o  (cost=0.00..293.57 rows=7010 width=12) (actual time=0.011..2.333 rows=7010 loops=1)
                                Filter: ((status)::text = 'ПЛАТЕНА'::text)
                                Rows Removed by Filter: 3036
                                Buffers: shared hit=168
Planning:
  Buffers: shared hit=46
Planning Time: 0.582 ms
Execution Time: 61.307 ms
}}}

'''Споредба:'''

|| Метрика || Вредност ||
|| Planning Time || 0.582 ms ||
|| Execution Time || 61.307 ms ||
|| Вкупно вратени редови || 24 (часови) ||
|| Тип на скенирање на orders || Seq Scan (со Filter на status) ||
|| Тип на скенирање на order_item || Seq Scan ||
|| Индекси искористени || (не се користат поради голем опсег на податоци) ||

'''Заклучок:''' Овој извештај враќа само 24 редови (часови), но мора да ги прочита сите 30.048 ставки и 7.010 платени нарачки. PostgreSQL избира Seq Scan бидејќи индексот не помага при агрегација на сите податоци.

=== Финален заклучок од анализата ===

|| Сценарио || Execution Time || Вратени редови || Индекси искористени ||
|| Најпрофитабилни производи || 110.770 ms || 24 || idx_orders_status ||
|| Месечни приходи || 18.309 ms || 13 || (Seq Scan) ||
|| Најпрометни часови || 61.307 ms || 24 || (Seq Scan) ||

'''Клучни наоди:'''

 * '''`idx_orders_status` се користи''' во Сценарио 1 преку `Bitmap Index Scan`, што покажува дека индексот е ефективен кога има филтер по статус.
 * '''Memoize оптимизација''' се користи за категориите во Сценарио 1, намалувајќи 20.935 повторени читања на само 5.
 * Во Сценарија 2 и 3, PostgreSQL избира Seq Scan бидејќи агрегацијата бара читање на сите податоци (нема селективен филтер).
 * Со растот на базата, индексите ќе имаат сè поголемо влијание, особено `idx_orders_status` и `idx_orders_created_at`.

== 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
}}}

Ова овозможува враќање на состојбата во случај на грешка или несреќа.