Changes between Version 1 and Version 2 of OtherTopics


Ignore:
Timestamp:
08/31/26 18:18:21 (7 days ago)
Author:
223091
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v1 v2  
    11= Other Topics =
    22
    3 This page documents the work completed for Phase P9 of the Room Reservation System project. The focus of this phase is performance analysis of complex SQL reports, proposed performance indexes, and security measures related to application and database access.
     3This page documents the work completed for Phase P9 of the Room Reservation System project.
     4
     5The focus of this phase is:
     6
     7 * performance analysis of the complex SQL reports from Phase P6
     8 * creation of indexes that support the complex reports
     9 * verification that PostgreSQL can use the created indexes through `EXPLAIN ANALYZE`
     10 * security measures at application and database level
    411
    512The phase is based on the complex reports created in Phase P6 and the advanced database objects created in Phase P7.
    613
    7 == SQL Performance ==
     14== SQL Performance Analysis ==
    815
    916The performance analysis was performed using PostgreSQL execution plans with:
    … …  
    1320}}}
    1421
    15 The goal was to compare the execution plans before and after creating indexes for the most important complex report queries. The tested reports are:
    16 
    17 * Report 1: Quarterly room utilization report
    18 * Report 2: Quarterly equipment demand and stock risk report
    19 
    20 Because the test database contains a relatively small amount of sample data, the absolute execution time is very low. However, the analysis still shows which tables are scanned, which joins are performed, and which indexes are useful for larger datasets.
    21 
    22 == Proposed indexes ==
    23 
    24 The following indexes were proposed and created to improve filtering, joining, and ordering in the complex reports and application scenarios.
     22The goal was to analyze the complex report queries from Phase P6 and to create indexes that support the filtering, joining, grouping and ordering used by those reports.
     23
     24The tested reports are:
     25
     26 * Report 1: Quarterly room utilization report
     27 * Report 2: Quarterly equipment demand and stock risk report
     28
     29Because the project database contains a small amount of sample data, PostgreSQL may sometimes choose sequential scans even when indexes exist. This is expected optimizer behavior, because reading a small table sequentially can be cheaper than using an index.
     30
     31For that reason, the documentation includes an additional index-usage verification plan using:
     32
     33{{{
     34SET enable_seqscan = off;
     35}}}
     36
     37This setting was used only for testing and documentation purposes, in order to prove that the created indexes are applicable to the query predicates and join conditions. After the verification queries, the setting was reset using:
     38
     39{{{
     40RESET enable_seqscan;
     41}}}
     42
     43== Created Performance Indexes ==
     44
     45The following indexes were created to improve filtering, joining and reporting performance.
    2546
    2647{{{
    2748CREATE INDEX IF NOT EXISTS idx_p9_reservations_room_date_status_time
    28 ON project.reservations (room_id, reservation_date, status, start_time, end_time);
     49ON project.reservations (room_id, reservation_date, status, start_time, end_time)
     50WHERE room_id IS NOT NULL;
    2951
    3052CREATE INDEX IF NOT EXISTS idx_p9_reservations_date_status_id
    … …  
    4769}}}
    4870
    49 The created indexes can be inspected in DBeaver.
    50 
    51 [[Image(p9_created_indexes.png, width=100%)]]
     71The created indexes were verified in PostgreSQL using the `pg_indexes` system view.
     72
     73{{{
     74SELECT
     75    schemaname,
     76    tablename,
     77    indexname,
     78    indexdef
     79FROM pg_indexes
     80WHERE schemaname = 'project'
     81  AND indexname LIKE 'idx_p9_%'
     82ORDER BY tablename, indexname;
     83}}}
     84
     85[[Image(p9_indexes_created_after_correction.png, width=100%)]]
    5286
    5387''Figure: Created performance indexes for Phase P9.''
    5488
    55 === Explanation of the proposed indexes ===
    56 
    57 The index ''idx_p9_reservations_room_date_status_time'' supports queries that search reservations by room, date, status, start time, and end time. This is useful for room availability checks and for detecting active room reservations in a selected time interval.
    58 
    59 The index ''idx_p9_reservations_date_status_id'' supports report queries that filter or group reservations by date and status. This is useful for quarterly reports and pending/approved reservation analysis.
    60 
    61 The index ''idx_p9_rooms_building_id'' supports joins between ''project.rooms'' and ''project.buildings''.
    62 
    63 The indexes on ''project.reservation_equipment'' support joins from reservations to requested equipment and from equipment to reservations.
    64 
    65 The index ''idx_p9_room_equipment_equipment_id'' supports queries that search which rooms contain a specific equipment type.
    66 
    67 The index ''idx_p9_approvals_reservation_id'' supports fast lookup of approval records for a reservation.
    68 
    69 == Report 1: Quarterly room utilization ==
     89=== Explanation of the created indexes ===
     90
     91The index `idx_p9_reservations_room_date_status_time` supports queries that search reservations by room, date, status, start time and end time. This is useful for room availability checks and for the quarterly room utilization report.
     92
     93The index `idx_p9_reservations_date_status_id` supports queries that filter reservations by date and status. This is useful for quarterly reports, reservation status analysis and equipment demand reporting.
     94
     95The index `idx_p9_rooms_building_id` supports joins between `project.rooms` and `project.buildings`.
     96
     97The indexes on `project.reservation_equipment` support joins between reservations and requested equipment. They are important for the equipment demand and stock risk report.
     98
     99The index `idx_p9_room_equipment_equipment_id` supports queries that calculate how much equipment is assigned to rooms.
     100
     101The index `idx_p9_approvals_reservation_id` supports faster lookup of approval records for a selected reservation.
     102
     103== Report 1: Quarterly Room Utilization ==
    70104
    71105=== Report description ===
    72106
    73 The first complex report analyzes room usage by quarter. It shows room reservations grouped by quarter, building, room, room type, and capacity. It also calculates reservation counts by status, requested hours, approved hours, percentage share of approved room usage, utilization ranking, and utilization level.
    74 
    75 This report is useful for identifying which rooms are used most often and which rooms have lower utilization.
    76 
    77 === SQL used for testing ===
    78 
    79 The report is executed through the view ''project.v_quarterly_room_utilization'', which was created in the previous phase.
    80 
    81 {{{
     107The first complex report analyzes room usage by quarter. It groups room reservations by quarter, building, room code, room type and capacity.
     108
     109The report calculates:
     110
     111 * total room reservations
     112 * approved reservations
     113 * rejected reservations
     114 * cancelled reservations
     115 * pending reservations
     116 * requested hours
     117 * approved hours
     118 * utilization ranking
     119 * utilization level
     120
     121This report is useful for identifying highly used rooms and rooms with lower utilization.
     122
     123=== SQL used for index verification ===
     124
     125The report reads data mainly from:
     126
     127 * `project.reservations`
     128 * `project.rooms`
     129 * `project.buildings`
     130
     131The most relevant created index for this report is:
     132
     133{{{
     134idx_p9_reservations_room_date_status_time
     135}}}
     136
     137The following query was executed with `EXPLAIN ANALYZE` to verify index usage.
     138
     139{{{
     140SET enable_seqscan = off;
     141
    82142EXPLAIN (ANALYZE, BUFFERS)
    83 SELECT *
    84 FROM project.v_quarterly_room_utilization
    85 ORDER BY quarter_start, utilization_rank, building_name, room_code;
    86 }}}
    87 
    88 === Execution plan before indexes ===
    89 
    90 Before creating the performance indexes, the report was executed with ''EXPLAIN (ANALYZE, BUFFERS)''. The execution plan shows the baseline performance of the query.
    91 
    92 [[Image(p9_report1_before_indexes.png, width=100%)]]
    93 
    94 ''Figure: Execution plan for Report 1 before creating performance indexes.''
    95 
    96 === Execution plan after indexes ===
    97 
    98 After creating the performance indexes, the same report was executed again with ''EXPLAIN (ANALYZE, BUFFERS)''.
    99 
    100 [[Image(p9_report1_after_indexes.png, width=100%)]]
    101 
    102 ''Figure: Execution plan for Report 1 after creating performance indexes.''
    103 
    104 === Performance discussion for Report 1 ===
    105 
    106 The first report reads data mainly from:
    107 
    108 * ''project.reservations''
    109 * ''project.rooms''
    110 * ''project.buildings''
    111 
    112 It groups reservations by quarter and room, calculates aggregate values, and ranks rooms by utilization.
    113 
    114 The most relevant indexes for this report are:
    115 
    116 * ''idx_p9_reservations_room_date_status_time''
    117 * ''idx_p9_reservations_date_status_id''
    118 * ''idx_p9_rooms_building_id''
    119 
    120 These indexes are useful because the report joins reservations with rooms and buildings, filters room-based reservations, and groups the results by date, room, and status.
    121 
    122 Since the dataset is small, PostgreSQL may still choose sequential scans or hash joins for some parts of the query because that can be cheaper for small tables. However, the indexes improve scalability and become more important when the number of reservations, rooms, and buildings increases.
    123 
    124 The execution plans before and after index creation were documented to show the difference in query planning and to verify that the report remains correct after adding indexes.
    125 
    126 == Report 2: Quarterly equipment demand and stock risk ==
     143SELECT
     144    date_trunc('quarter', res.reservation_date)::date AS quarter_start,
     145    b.name AS building_name,
     146    r.room_code,
     147    r.type,
     148    r.capacity,
     149    COUNT(*) AS total_room_reservations,
     150    COUNT(*) FILTER (WHERE res.status = 'approved') AS approved_reservations,
     151    COUNT(*) FILTER (WHERE res.status = 'rejected') AS rejected_reservations,
     152    COUNT(*) FILTER (WHERE res.status = 'cancelled') AS cancelled_reservations,
     153    COUNT(*) FILTER (WHERE res.status = 'pending') AS pending_reservations,
     154    SUM(EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0) AS requested_hours,
     155    COALESCE(
     156        SUM(EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0)
     157        FILTER (WHERE res.status = 'approved'),
     158        0
     159    ) AS approved_hours
     160FROM project.reservations res
     161JOIN project.rooms r
     162    ON res.room_id = r.room_id
     163JOIN project.buildings b
     164    ON r.building_id = b.building_id
     165WHERE res.room_id IS NOT NULL
     166  AND res.reservation_date >= DATE '2026-01-01'
     167  AND res.reservation_date < DATE '2027-01-01'
     168GROUP BY
     169    date_trunc('quarter', res.reservation_date)::date,
     170    b.name,
     171    r.room_code,
     172    r.type,
     173    r.capacity
     174ORDER BY
     175    quarter_start,
     176    building_name,
     177    room_code;
     178
     179RESET enable_seqscan;
     180}}}
     181
     182=== Execution plan and index usage ===
     183
     184[[Image(p9_report1_index_usage_explain.png, width=100%)]]
     185
     186''Figure: EXPLAIN ANALYZE plan for the room utilization report showing index usage.''
     187
     188The important part of the execution plan is:
     189
     190{{{
     191Index Only Scan using idx_p9_reservations_room_date_status_time
     192}}}
     193
     194This proves that PostgreSQL can use the created index for the room utilization report.
     195
     196The index supports the report because the query filters room-based reservations by reservation date and then joins the result with rooms and buildings.
     197
     198== Report 2: Quarterly Equipment Demand and Stock Risk ==
    127199
    128200=== Report description ===
    129201
    130 The second complex report analyzes equipment demand by quarter. It compares the requested equipment quantities with the available general stock and the equipment assigned to rooms. The report helps identify equipment types with higher demand and possible stock risk.
    131 
    132 This report is useful for deciding whether additional equipment should be purchased or whether existing equipment is enough for future reservations.
    133 
    134 === SQL used for testing ===
    135 
    136 {{{
     202The second complex report analyzes equipment demand by quarter. It compares requested equipment quantities with available equipment stock and equipment assigned to rooms.
     203
     204The report calculates:
     205
     206 * total registered equipment quantity
     207 * number of reservations with equipment
     208 * total requested quantity
     209 * approved requested quantity
     210 * pending requested quantity
     211 * rejected requested quantity
     212 * demand-to-registered percentage
     213 * demand rank
     214 * demand level
     215
     216This report is useful for identifying equipment types with high demand or possible stock risk.
     217
     218=== SQL used for index verification ===
     219
     220The report reads data mainly from:
     221
     222 * `project.reservation_equipment`
     223 * `project.reservations`
     224 * `project.equipment`
     225 * `project.room_equipment`
     226
     227The most relevant created indexes for this report are:
     228
     229{{{
     230idx_p9_reservations_date_status_id
     231idx_p9_reservation_equipment_reservation_id
     232idx_p9_reservation_equipment_equipment_id
     233idx_p9_room_equipment_equipment_id
     234}}}
     235
     236The following query was executed with `EXPLAIN ANALYZE` to verify index usage.
     237
     238{{{
     239SET enable_seqscan = off;
     240
    137241EXPLAIN (ANALYZE, BUFFERS)
    138242WITH room_stock AS (
    139 SELECT
    140 re.equipment_id,
    141 SUM(re.quantity) AS assigned_room_quantity
    142 FROM project.room_equipment re
    143 GROUP BY re.equipment_id
     243    SELECT
     244        re.equipment_id,
     245        SUM(re.quantity) AS assigned_room_quantity
     246    FROM project.room_equipment re
     247    GROUP BY re.equipment_id
    144248),
    145249equipment_demand AS (
    146 SELECT
    147 date_trunc('quarter', res.reservation_date)::date AS quarter_start,
    148 e.equipment_id,
    149 e.name AS equipment_name,
    150 e.stock_quantity,
    151 COALESCE(rs.assigned_room_quantity, 0) AS assigned_room_quantity,
    152 e.stock_quantity + COALESCE(rs.assigned_room_quantity, 0) AS total_registered_quantity,
    153 COUNT(DISTINCT res.reservation_id) AS reservations_with_equipment,
    154 SUM(req.requested_quantity) AS total_requested_quantity,
    155 SUM(req.requested_quantity) FILTER (WHERE res.status = 'approved') AS approved_requested_quantity,
    156 SUM(req.requested_quantity) FILTER (WHERE res.status = 'pending') AS pending_requested_quantity,
    157 SUM(req.requested_quantity) FILTER (WHERE res.status = 'rejected') AS rejected_requested_quantity
    158 FROM project.reservation_equipment req
    159 JOIN project.reservations res
    160 ON req.reservation_id = res.reservation_id
    161 JOIN project.equipment e
    162 ON req.equipment_id = e.equipment_id
    163 LEFT JOIN room_stock rs
    164 ON e.equipment_id = rs.equipment_id
    165 WHERE res.reservation_date >= DATE '2026-01-01'
    166 AND res.reservation_date < DATE '2027-01-01'
    167 GROUP BY
    168 date_trunc('quarter', res.reservation_date)::date,
    169 e.equipment_id,
    170 e.name,
    171 e.stock_quantity,
    172 rs.assigned_room_quantity
     250    SELECT
     251        date_trunc('quarter', res.reservation_date)::date AS quarter_start,
     252        e.equipment_id,
     253        e.name AS equipment_name,
     254        e.stock_quantity,
     255        COALESCE(rs.assigned_room_quantity, 0) AS assigned_room_quantity,
     256        e.stock_quantity + COALESCE(rs.assigned_room_quantity, 0) AS total_registered_quantity,
     257        COUNT(DISTINCT req.reservation_id) AS reservations_with_equipment,
     258        SUM(req.requested_quantity) AS total_requested_quantity,
     259        COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'approved'), 0) AS approved_requested_quantity,
     260        COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'pending'), 0) AS pending_requested_quantity,
     261        COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'rejected'), 0) AS rejected_requested_quantity
     262    FROM project.reservation_equipment req
     263    JOIN project.reservations res
     264        ON req.reservation_id = res.reservation_id
     265    JOIN project.equipment e
     266        ON req.equipment_id = e.equipment_id
     267    LEFT JOIN room_stock rs
     268        ON e.equipment_id = rs.equipment_id
     269    WHERE res.reservation_date >= DATE '2026-01-01'
     270      AND res.reservation_date < DATE '2027-01-01'
     271    GROUP BY
     272        date_trunc('quarter', res.reservation_date)::date,
     273        e.equipment_id,
     274        e.name,
     275        e.stock_quantity,
     276        rs.assigned_room_quantity
    173277)
    174278SELECT
    175 quarter_start,
    176 equipment_name,
    177 stock_quantity,
    178 assigned_room_quantity,
    179 total_registered_quantity,
    180 reservations_with_equipment,
    181 total_requested_quantity,
    182 approved_requested_quantity,
    183 pending_requested_quantity,
    184 rejected_requested_quantity,
    185 ROUND(total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0), 2) AS demand_to_supply_ratio,
    186 DENSE_RANK() OVER (
    187 PARTITION BY quarter_start
    188 ORDER BY total_requested_quantity DESC, equipment_name
    189 ) AS demand_rank,
    190 CASE
    191 WHEN total_requested_quantity > total_registered_quantity THEN 'stock_risk'
    192 WHEN total_requested_quantity >= total_registered_quantity * 0.75 THEN 'high_demand'
    193 WHEN total_requested_quantity > 0 THEN 'normal_demand'
    194 ELSE 'no_demand'
    195 END AS demand_level
     279    quarter_start,
     280    equipment_name,
     281    stock_quantity,
     282    assigned_room_quantity,
     283    total_registered_quantity,
     284    reservations_with_equipment,
     285    total_requested_quantity,
     286    approved_requested_quantity,
     287    pending_requested_quantity,
     288    rejected_requested_quantity,
     289    ROUND(
     290        total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0) * 100,
     291        2
     292    ) AS demand_to_registered_percent,
     293    DENSE_RANK() OVER (
     294        PARTITION BY quarter_start
     295        ORDER BY total_requested_quantity DESC, reservations_with_equipment DESC, equipment_name
     296    ) AS demand_rank,
     297    CASE
     298        WHEN total_requested_quantity > total_registered_quantity THEN 'stock_risk'
     299        WHEN total_requested_quantity >= total_registered_quantity * 0.75 THEN 'high_demand'
     300        WHEN total_requested_quantity > 0 THEN 'normal_demand'
     301        ELSE 'no_demand'
     302    END AS demand_level
    196303FROM equipment_demand
    197304ORDER BY quarter_start, demand_rank, equipment_name;
    198 }}}
    199 
    200 === Execution plan before indexes ===
    201 
    202 Before creating the performance indexes, the second report was executed with ''EXPLAIN (ANALYZE, BUFFERS)''.
    203 
    204 [[Image(p9_report2_before_indexes.png, width=100%)]]
    205 
    206 ''Figure: Execution plan for Report 2 before creating performance indexes.''
    207 
    208 === Execution plan after indexes ===
    209 
    210 After creating the performance indexes, the same report was executed again with ''EXPLAIN (ANALYZE, BUFFERS)''.
    211 
    212 [[Image(p9_report2_after_indexes.png, width=100%)]]
    213 
    214 ''Figure: Execution plan for Report 2 after creating performance indexes.''
    215 
    216 === Performance discussion for Report 2 ===
    217 
    218 The second report reads data mainly from:
    219 
    220 * ''project.reservation_equipment''
    221 * ''project.reservations''
    222 * ''project.equipment''
    223 * ''project.room_equipment''
    224 
    225 The report joins reservation equipment with reservations and equipment, aggregates requested quantities, calculates total registered equipment quantity, and ranks equipment types by demand.
    226 
    227 The most relevant indexes for this report are:
    228 
    229 * ''idx_p9_reservations_date_status_id''
    230 * ''idx_p9_reservation_equipment_reservation_id''
    231 * ''idx_p9_reservation_equipment_equipment_id''
    232 * ''idx_p9_room_equipment_equipment_id''
    233 
    234 These indexes are useful because the query joins reservation equipment to reservations and equipment, filters reservations by date, and aggregates equipment demand by equipment type.
    235 
    236 As with the first report, the sample database is small, so PostgreSQL may still choose sequential scans or hash joins where this is cheaper. The important point is that the indexes are now available for larger datasets and for selective searches on reservation dates, equipment identifiers, and reservation identifiers.
    237 
    238 == SQL performance conclusion ==
    239 
    240 The performance analysis shows that the complex reports work correctly before and after adding indexes. The indexes were selected based on the columns used in joins, filtering, grouping, and ordering.
    241 
    242 For a small test database, execution times are already low, so the performance gain may not be large. However, the proposed indexes are important for scalability because the same reports would become more expensive when the database contains many reservations, rooms, equipment records, and approval records.
     305
     306RESET enable_seqscan;
     307}}}
     308
     309=== Execution plan and index usage ===
     310
     311[[Image(p9_report2_index_usage_explain.png, width=100%)]]
     312
     313''Figure: EXPLAIN ANALYZE plan for the equipment demand report showing index usage.''
     314
     315The important part of the execution plan is:
     316
     317{{{
     318Index Scan using idx_p9_reservation_equipment_equipment_id
     319}}}
     320
     321This proves that PostgreSQL can use the created index for the equipment demand report.
     322
     323The index supports the report because the query joins requested equipment records with equipment records and aggregates demand by equipment type.
     324
     325== Discussion About Small Dataset and Sequential Scans ==
     326
     327The current project database has a small number of sample rows. Because of this, PostgreSQL may normally choose sequential scans or hash joins for some queries, even when indexes exist.
     328
     329This does not mean that the indexes are incorrect. It means that for very small tables, the optimizer estimates that reading the whole table is cheaper than using an index.
     330
     331To explicitly demonstrate that the created indexes match the query predicates and join conditions, the verification queries were executed with:
     332
     333{{{
     334SET enable_seqscan = off;
     335}}}
     336
     337This was used only during testing. It was reset immediately after the verification queries with:
     338
     339{{{
     340RESET enable_seqscan;
     341}}}
     342
     343The execution plans show that the created indexes are applicable and can be used by PostgreSQL. The indexes become more important as the number of reservations, rooms, equipment records and approval records increases.
     344
     345== SQL Performance Conclusion ==
     346
     347The performance analysis shows that the complex reports from Phase P6 are supported by indexes that match their filtering and joining patterns.
    243348
    244349The most important indexed access patterns are:
    245350
    246 * searching reservations by room, date, status, and time interval;
    247 * grouping reservations by date and status;
    248 * joining rooms with buildings;
    249 * joining reservations with requested equipment;
    250 * joining equipment with room equipment;
    251 * finding approvals by reservation.
    252 
    253 == Security measures ==
     351 * searching reservations by room and reservation date
     352 * filtering reservations by date and status
     353 * joining rooms with buildings
     354 * joining reservations with requested equipment
     355 * joining equipment with requested equipment
     356 * calculating equipment assigned to rooms
     357 * finding approvals by reservation
     358
     359The execution plans demonstrate that PostgreSQL can use the created indexes. This improves the scalability of the project and makes the complex reports more suitable for larger datasets.
     360
     361== Security Measures ==
    254362
    255363This project includes security measures at both the application level and the database level.
    … …  
    257365=== Application-level security measures ===
    258366
    259 The Java prototype uses JDBC ''PreparedStatement'' objects for SQL statements that receive user input. This prevents SQL injection because user input is passed as parameters instead of being concatenated directly into SQL strings.
     367The final application is implemented with a Spring Boot backend. Database access is performed through Spring `JdbcTemplate`.
     368
     369The backend uses parameterized SQL queries. User input is passed as query parameters instead of being concatenated directly into SQL strings. This reduces the risk of SQL injection.
    260370
    261371Examples of parameterized inputs include:
    262372
    263 * reservation date;
    264 * start time and end time;
    265 * selected room;
    266 * selected user;
    267 * selected equipment;
    268 * approval decision;
    269 * approval note.
    270 
    271 The database password is not hard-coded in the source code. The application asks for the database password when it starts. This reduces the risk of exposing credentials in the repository or documentation.
    272 
    273 The application connects to the database through the assigned SSH tunnel and uses the database credentials assigned through the EPRMS system.
     373 * username or email during login
     374 * registration data
     375 * reservation date
     376 * start time and end time
     377 * selected room
     378 * selected equipment
     379 * approval decision
     380 * approval note
     381
     382The database password is not hard-coded in the source code. It is provided through an environment variable:
     383
     384{{{
     385SPRING_DATASOURCE_PASSWORD
     386}}}
     387
     388The database URL and username are also configured through environment variables:
     389
     390{{{
     391SPRING_DATASOURCE_URL
     392SPRING_DATASOURCE_USERNAME
     393}}}
     394
     395This prevents sensitive credentials from being committed to the Git repository.
     396
     397The application also uses BCrypt password hashing. User passwords are not stored as plain text. The application stores only the password hash in the `project.user_credentials` table.
    274398
    275399=== Database-level security measures ===
    276400
    277 The database also contains protection logic implemented in Phase P7:
    278 
    279 * only users with role ''admin'' or ''approver'' can approve or reject reservations;
    280 * direct invalid approval inserts are rejected by a database trigger;
    281 * approval decisions are restricted through the custom domain ''project.approval_decision_domain'';
    282 * reservation overlap is prevented at database level;
    283 * empty reservations without both room and equipment are rejected;
    284 * requested equipment quantity is checked against available stock.
    285 
    286 These measures are important because they protect the database even when data is inserted directly through DBeaver or another database client, not only through the Java prototype.
    287 
    288 == Unauthorized approval rejection test ==
    289 
    290 To test database-level security, an unauthorized approval attempt was executed. The test tries to insert or create an approval using a user who does not have the required role.
     401The database contains additional protection logic implemented in Phase P7.
     402
     403The most important database-level security and consistency measures are:
     404
     405 * only users with role `admin` or `approver` can approve or reject reservations
     406 * invalid approval decisions are rejected by the custom domain `project.approval_decision_domain`
     407 * approval logic is handled through the stored function `project.fn_approve_or_reject_reservation`
     408 * reservation overlap is prevented at database level
     409 * empty reservations without both room and equipment are rejected
     410 * requested equipment quantity is checked against available stock
     411 * invalid direct writes are rejected by triggers
     412
     413These measures protect the database even if someone tries to insert data directly through DBeaver or another database client.
     414
     415== Unauthorized Approval Rejection Test ==
     416
     417To test database-level security, an unauthorized approval attempt was executed. The test tries to insert an approval using a user who does not have the required role.
    291418
    292419Example of an invalid approval attempt:
    … …  
    294421{{{
    295422INSERT INTO project.approvals (
    296 reservation_id,
    297 approver_id,
    298 decision,
    299 decision_time,
    300 note
     423    reservation_id,
     424    approver_id,
     425    decision,
     426    decision_time,
     427    note
    301428)
    302429VALUES (
    303 7,
    304 1,
    305 'approved',
    306 CURRENT_TIMESTAMP,
    307 'Unauthorized approval attempt.'
     430    7,
     431    1,
     432    'approved',
     433    CURRENT_TIMESTAMP,
     434    'Unauthorized approval attempt.'
    308435);
    309436}}}
    … …  
    317444=== Security discussion ===
    318445
    319 The unauthorized approval test confirms that approval security is enforced inside the database. Even if someone tries to bypass the Java application and insert an approval directly into the ''project.approvals'' table, the database checks the role of the approver and rejects invalid writes.
     446The unauthorized approval test confirms that approval security is enforced inside the database.
     447
     448Even if someone tries to bypass the Spring Boot application and insert an approval directly into the `project.approvals` table, the database checks the role of the approver and rejects invalid writes.
    320449
    321450This is stronger than only checking the rule in the application, because the rule remains active for all database clients.
    322451
    323 == Other developments ==
     452== Other Developments ==
    324453
    325454No additional optional topic was added in this phase. The main focus was performance analysis with indexes and security measures for application and database access.
    326455
    327 == Final conclusion ==
    328 
    329 Phase P9 improves the project in two important directions.
    330 
    331 First, the complex reports from Phase P6 were analyzed using PostgreSQL execution plans. Indexes were proposed and created for the most important filtering and join columns. The execution plans before and after index creation were documented with screenshots.
    332 
    333 Second, the project security was documented and tested. The application uses parameterized SQL statements and does not store the database password in the source code. The database enforces important security and consistency rules through triggers, stored functions, and a custom domain.
    334 
    335 Together, these additions make the Room Reservation System more scalable, more reliable, and better protected against invalid or unauthorized database operations.
     456== Final Conclusion ==
     457
     458Phase P9 improves the Room Reservation System in two important directions.
     459
     460First, the complex reports from Phase P6 were analyzed using PostgreSQL execution plans. Indexes were created for the most important filtering and join columns. The documentation now includes explicit index-usage evidence through `EXPLAIN ANALYZE`.
     461
     462Second, the project security was documented and tested. The Spring Boot backend uses parameterized SQL access through `JdbcTemplate`, environment-based credentials and BCrypt password hashing. The database enforces important security and consistency rules through triggers, stored functions and a custom domain.
     463
     464Together, these additions make the Room Reservation System more scalable, more reliable and better protected against invalid or unauthorized database operations.