= Other topics (Performance, Security, …)

== Scenario 1 - Top Selling Products and Restock Plan

{{{#!div style="text-align: justify; width: 100%;"

==== Without indexes

The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both {{{orders}}} and {{{order_products}}}.

{{{
Seq Scan on order_products
rows=75021

Seq Scan on orders
rows=5009
Rows Removed by Filter: 20008
}}}

The query was executed 10 times and the average execution time without indexes was: **25.646 ms**

==== Indexes
{{{
CREATE INDEX idx_orders_status_purchase_date
ON project.orders (status, purchase_date, order_id);

CREATE INDEX idx_order_products_order_product_quantity
ON project.order_products (order_id, product_id, quantity);

ANALYZE project.orders;
ANALYZE project.order_products;
}}}

* The first index targets the filters on {{{status}}} and {{{purchase_date}}}, while also including {{{order_id}}} for the join.

* The second index was tested to support the join and aggregation on {{{order_products}}}.

==== With indexes

After indexing, PostgreSQL used:

{{{
Index Only Scan using idx_orders_status_purchase_date on orders
Heap Fetches: 0
}}}

This replaced the previous sequential scan on orders.

However, PostgreSQL did not use {{{idx_order_products_order_product_quantity}}}. It continued using:

{{{
Seq Scan on order_products
}}}

because scanning the table and performing a hash join was estimated to be cheaper.

The query was again executed 10 times and the average execution time with indexes was: **22.299 ms**

==== Performance comparison and conclusion
{{{
Without indexes: 25.646 ms
With indexes:    22.299 ms
Improvement:     13.05%
}}}

* {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and reduced the cost of filtering orders by status and purchase date.

* {{{idx_order_products_order_product_quantity}}} was not used by the optimizer, because a sequential scan of order_products was still considered cheaper for the current dataset.

}}}

== Scenario 2 - Slow Moving Products

{{{#!div style="text-align: justify; width: 100%;"

==== Without indexes

The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used a sequential scan on {{{orders}}} to find orders from the last 6 months with a completed status.

{{{
Seq Scan on orders
rows=2549
Rows Removed by Filter: 22468
}}}

The existing primary-key index on {{{order_products}}} was already used:

{{{
Index Only Scan using order_products_pk on order_products
}}}

The query was executed 10 times and the average execution time without indexes was: **12.125 ms**

==== Indexes

{{{
CREATE INDEX idx_orders_status_purchase_date
ON project.orders (status, purchase_date, order_id);

ANALYZE project.orders;
}}}

The index targets the {{{status}}} and {{{purchase_date}}} filters and also includes {{{order_id}}} for the join with {{{order_products}}}.

==== With indexes

After indexing, PostgreSQL used:

{{{
Index Only Scan using idx_orders_status_purchase_date on orders
Heap Fetches: 0
}}}

This replaced the sequential scan on {{{orders}}} and reduced the number of pages that had to be read.

The existing {{{order_products_pk}}} index continued to be used for the join with {{{order_products}}}.

The query was again executed 10 times and the average execution time with indexes was: **8.894 ms**

==== Performance comparison and conclusion

{{{
Without indexes: 12.125 ms
With indexes: 8.894 ms
Improvement: 26.65%
}}}

{{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and improved the filtering of orders by status and purchase date.
The query improved by approximately 26.65%, while the existing {{{order_products_pk}}} index continued to support the join efficiently.

}}}

== Scenario 3 - Impact of Admin Discounts on Sales Numbers

{{{#!div style="text-align: justify; width: 100%;"

==== Without indexes

The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both {{{orders}}} and {{{order_products}}} while calculating the 30-day periods before and after each discount.

{{{
Seq Scan on order_products
rows=75021

Seq Scan on orders
rows=5009
Rows Removed by Filter: 20008
}}}

The query was executed 10 times and the average execution time without indexes was: **103.004 ms**

==== Indexes

{{{
CREATE INDEX idx_order_products_product_order
ON project.order_products (product_id, order_id)
INCLUDE (quantity, price_at_purchase);

CREATE INDEX idx_orders_status_purchase_date
ON project.orders (status, purchase_date, order_id);

ANALYZE project.order_products;
ANALYZE project.orders;
}}}

* {{{idx_order_products_product_order}}} was tested to support lookups of order products by {{{product_id}}} and provide the quantity and purchase price required by the aggregation.

* {{{idx_orders_status_purchase_date}}} targets the order status filter and includes the purchase date and {{{order_id}}} needed for the sales-period joins.

==== With indexes

PostgreSQL used:

{{{
Index Only Scan using idx_orders_status_purchase_date on orders
Heap Fetches: 0
}}}

The index was used for both the pre-promotion and post-promotion order lookups.

However, {{{idx_order_products_product_order}}} was not used. PostgreSQL continued using:

{{{
Seq Scan on order_products
}}}

because scanning the table and performing the hash joins was estimated to be cheaper for the current data distribution.

The query was executed 10 times with both indexes present and the average execution time was: **97.710 ms**

The unused {{{idx_order_products_product_order}}} index was removed after testing.

==== Performance comparison and conclusion

{{{
Without indexes: 103.004 ms
With indexes: 97.710 ms
Improvement: 5.14%
}}}

* {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan in both the pre-promotion and post-promotion parts of the query.

* {{{idx_order_products_product_order}}} was not used by the optimizer and was removed.

}}}

== Scenario 4 - Customer Habits and Points Spending

==== Without indexes

The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used a sequential scan on {{{orders}}} and scanned all {{{order_products}}} records to calculate order totals.

{{{
Seq Scan on orders
rows=5009
Rows Removed by Filter: 20008

Seq Scan on order_products
rows=75021
}}}

The {{{order_products}}} aggregation also required temporary disk usage:

{{{
HashAggregate
Batches: 5
Disk Usage: 760kB
}}}

The query was executed 10 times and the average execution time without indexes was: **93.895 ms**

==== Indexes

{{{
CREATE INDEX idx_orders_status_purchase_date
ON project.orders (status, purchase_date, order_id);

ANALYZE project.orders;
}}}

The index targets the {{{status}}} and {{{purchase_date}}} filters on {{{orders}}} and includes {{{order_id}}} for the join with aggregated order totals.

==== With indexes

After indexing, PostgreSQL used:

{{{
Bitmap Index Scan on idx_orders_status_purchase_date

Bitmap Heap Scan on orders
}}}

This replaced the sequential scan on {{{orders}}}.

However, {{{order_products}}} was still processed using:

{{{
Seq Scan on order_products
rows=75021
}}}

because the query needs to aggregate essentially the whole {{{order_products}}} table to calculate total spend per order.

The query was executed 10 times and the average execution time with indexes was: **90.547 ms**

==== Performance comparison and conclusion

{{{
Without indexes: 93.895 ms
With indexes: 90.547 ms
Improvement: 3.57%
}}}

* {{{idx_orders_status_purchase_date}}} was successfully used through a Bitmap Index Scan and reduced the cost of filtering orders.

* The overall improvement was approximately 3.57% because the main remaining cost is the full aggregation of {{{order_products}}}, which still requires a sequential scan and temporary disk usage.

== Security

{{{#!div style="text-align: justify; width: 100%;"
==== Cookie-based Authentication

For authentication in our application, we use ASP.NET Core cookie authentication.

After a user successfully logs in, the server creates an authentication cookie containing information about the authenticated user. This allows the application to recognize the user on subsequent requests without requiring them to log in again for every request.

Cookie authentication is configured in {{{Program.cs}}}:

{{{
builder.Services
    .AddAuthentication("Cookies")
    .AddCookie("Cookies", options =>
    {
        options.LoginPath = "/Account/Login";
        options.AccessDeniedPath = "/Account/Login";
        options.ExpireTimeSpan = TimeSpan.FromHours(8);
        options.SlidingExpiration = true;
    });

builder.Services.AddAuthorization();
}}}

The authentication cookie is valid for 8 hours. Sliding expiration is enabled, meaning the authentication period can be renewed while the user remains active.

After successful login, claims containing information about the user are created:

{{{
var claims = new List<Claim>
{
    new Claim(
        ClaimTypes.NameIdentifier,
        user.UserId.ToString()),

    new Claim(
        ClaimTypes.Name,
        user.Username),

    new Claim(
        ClaimTypes.Email,
        user.Email),

    new Claim(
        ClaimTypes.Role,
        role)
};
}}}

An identity and authentication principal are then created:

{{{
var identity = new ClaimsIdentity(
    claims,
    "Cookies");

var principal =
    new ClaimsPrincipal(identity);

await HttpContext.SignInAsync(
    "Cookies",
    principal);
}}}

The role claim allows us to distinguish between consumers and administrators and can be used to restrict access to specific functionality.

When the user logs out, the authentication cookie is invalidated:

{{{
await HttpContext.SignOutAsync("Cookies");

HttpContext.Session.Clear();
}}}

==== Password Storage

For password hashing, we use ASP.NET Core's {{{PasswordHasher<User>}}}:

{{{
builder.Services.AddScoped<
    IPasswordHasher<User>,
    PasswordHasher<User>>();
}}}

When a new user registers, their password is hashed before it is stored in the database:

{{{
user.Password =
    _passwordHasher.HashPassword(
        user,
        model.Password);

_context.Users.Add(user);
_context.SaveChanges();
}}}

Because password hashing is a one-way operation, the original password cannot be obtained from the stored value.

During login, we first retrieve the user by username:

{{{
var user = _context.Users
    .FirstOrDefault(x =>
        x.Username == model.Username);
}}}

The entered password is then verified against the stored password hash:

{{{
var result =
    _passwordHasher.VerifyHashedPassword(
        user,
        user.Password,
        model.Password);

if (result == PasswordVerificationResult.Failed)
{
    ModelState.AddModelError(
        "",
        "Invalid username or password.");

    return View(model);
}
}}}

This allows the application to verify a password without ever storing or comparing plaintext passwords in the database.

==== Protection Against CSRF

For POST requests that modify application data, ASP.NET Core anti-forgery protection is used.

Controller actions that receive POST requests are marked with:

{{{
[HttpPost]
[ValidateAntiForgeryToken]
}}}

For example:

{{{
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Register(RegisterViewModel model)
{
    // ...
}
}}}

The anti-forgery token protects the application against Cross-Site Request Forgery (CSRF) attacks by ensuring that the submitted request originates from a valid application form.

==== HTTPS and HSTS

The application redirects HTTP requests to HTTPS:

{{{
app.UseHttpsRedirection();
}}}

Additionally, outside the development environment, HTTP Strict Transport Security (HSTS) is enabled:

{{{
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}
}}}

HTTPS protects communication between the browser and the server by encrypting transmitted information, while HSTS instructs browsers to use HTTPS when communicating with the application.
}}}