Bài 62: Products CRUD Hoàn Chỉnh — ETag + Soft Delete

Sau bài học, bạn sẽ:

• Implement ETag header cho GET /api/v1/products/{slug} + If-None-Match trả 304 Not Modified.
• Tạo migration thêm cột deleted_at TIMESTAMPTZ cho soft delete + partial index + trigger updated_at.
• Refactor delete_product từ hard delete → soft delete (UPDATE deleted_at).
• Implement POST /products/{slug}/restore endpoint admin un-delete.
• Tạo trigger Postgres update_updated_at_column auto-refresh + bảng audit_logs 7 column.
• Thêm filter ?include_deleted=true cho admin view, default exclude với user.
• Verify pattern reuse cho orders/users/cart trong các bài Group 7 tiếp theo.

Tạo migration thứ 6 trong workspace Shop API (5 migration đã có sau Group 6: create_products, add_products_tags, create_orders, create_payments, add_products_metadata):

sqlx migrate add --source crates/shop-db/migrations add_products_soft_delete
# Creating crates/shop-db/migrations/20260615160000_add_products_soft_delete.sql

Mở file SQL vừa tạo và viết schema change:

-- File: crates/shop-db/migrations/20260615160000_add_products_soft_delete.sql

-- Soft delete column
ALTER TABLE products
    ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index cho query active products
CREATE INDEX products_active_idx ON products(deleted_at)
    WHERE deleted_at IS NULL;

-- Trigger function auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger bind products
CREATE TRIGGER products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Apply migration:

sqlx migrate run --source crates/shop-db/migrations
# Applied 20260615160000/migrate add_products_soft_delete

3 quyết định lock vĩnh viễn từ migration này:

• deleted_at TIMESTAMPTZ NULLABLE — NULL = row active, NOT NULL = soft deleted (timestamp lúc xoá). Pattern lock cho mọi entity Shop API cần soft delete (products, orders, users, categories, reviews). Cart KHÔNG cần soft delete (dữ liệu ephemeral, xoá hẳn khi user clear).
• Partial index WHERE deleted_at IS NULL — chỉ index row active. Đa số query là "list active products" → partial index giảm 30-50% size so với full index, tăng tốc scan vì B-tree nhỏ hơn. Postgres tự dùng partial index khi WHERE clause match điều kiện.
• Trigger update_updated_at_column — auto refresh updated_at mỗi UPDATE tại DB level. KHÔNG set updated_at = NOW() thủ công trong Rust handler — dev quên là updated_at stale, ETag sai, cache nhầm. Trigger guarantee consistency cross mọi mutation (handler HTTP, admin SQL trực tiếp, worker job).

Hàm update_updated_at_column() reuse cho mọi bảng cần auto-update updated_at — viết 1 lần dùng N lần. Khi tạo bảng mới (orders, users, ...) chỉ cần CREATE TRIGGER <table>_updated_at BEFORE UPDATE ON <table> FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); trong migration tương ứng.

Audit log là requirement bắt buộc cho mọi e-commerce production: ai đổi gì lúc nào, đặc biệt cho admin action sensitive. Tạo migration thứ 7:

sqlx migrate add --source crates/shop-db/migrations create_audit_log
# Creating crates/shop-db/migrations/20260615160100_create_audit_log.sql

Schema bảng audit_logs:

-- File: crates/shop-db/migrations/20260615160100_create_audit_log.sql

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    row_id BIGINT NOT NULL,
    action TEXT NOT NULL
        CHECK (action IN ('insert', 'update', 'delete', 'restore')),
    actor_user_id BIGINT,         -- NULL cho system action
    changes JSONB,                -- old vs new value diff
    request_id TEXT,              -- correlation HTTP request (B39)
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX audit_logs_table_row_idx
    ON audit_logs(table_name, row_id, created_at DESC);
CREATE INDEX audit_logs_actor_idx
    ON audit_logs(actor_user_id, created_at DESC);
CREATE INDEX audit_logs_request_idx
    ON audit_logs(request_id);

Giải nghĩa 7 column:

• table_name + row_id — loose reference đến row được audit. KHÔNG dùng foreign key cứng để tránh coupling 1 bảng audit với nhiều entity khác nhau (products, orders, users, categories), và cho phép giữ audit log khi row gốc bị hard-delete sau N năm.
• action — 4 enum value insert/update/delete/restore map đúng vào CRUD operation Shop API.
• actor_user_id — NULL cho system action (cron job, webhook), có user_id cho user action (admin update product, customer cancel order).
• changes JSONB — diff old vs new value dạng JSON object. JSONB binary format cho phép query changes -> 'stock' để filter audit theo field.
• request_id — correlation với HTTP request từ middleware B39. Khi có incident, trace audit log từ một request cụ thể qua field này.
• created_at — timestamp ghi log, default NOW().

3 index phục vụ 3 query pattern phổ biến: (a) audit per resource "lịch sử thay đổi của product X" qua (table_name, row_id, created_at DESC); (b) audit per actor "admin Y đã làm gì hôm qua" qua (actor_user_id, created_at DESC); (c) trace per request "request UUID Z gây ra những thay đổi gì" qua request_id.

Lock decision Shop API: audit log ghi qua service layer Rust chứ KHÔNG qua trigger Postgres. Lý do: service layer có sẵn request_id từ middleware Extension, có sẵn actor_user_id từ auth context, control transaction boundary chính xác. Trigger Postgres không biết context HTTP request, phải truyền qua session variable phức tạp — lợi không bù bất tiện.

Audit log áp dụng cho products, orders, users, payments — các entity có business value cao. Cart KHÔNG audit (ephemeral data, user thường xuyên thêm/xoá item, audit log sẽ phình vô ích).

Tạo file helper riêng cho ETag logic:

// File: crates/shop-api/src/etag.rs

use axum::http::{header, HeaderMap};
use chrono::{DateTime, Utc};

/// Build weak ETag từ updated_at timestamp.
/// Format theo RFC 9110 section 8.8.3: W/"<identifier>"
pub fn weak_etag(updated_at: DateTime<Utc>) -> String {
    format!(r#"W/"{}""#, updated_at.timestamp())
}

/// Check header If-None-Match match với ETag hiện tại.
pub fn matches_etag(headers: &HeaderMap, current: &str) -> bool {
    headers
        .get(header::IF_NONE_MATCH)
        .and_then(|v| v.to_str().ok())
        .map(|v| v.trim() == current)
        .unwrap_or(false)
}

Wire module trong crates/shop-api/src/lib.rs (hoặc main.rs nếu file gốc Shop API):

// File: crates/shop-api/src/lib.rs (extend)
pub mod etag;

Refactor handler get_product trong crates/shop-api/src/routes/products.rs để integrate ETag:

// File: crates/shop-api/src/routes/products.rs (refactor get_product)
use axum::extract::State;
use axum::http::{header, HeaderMap, StatusCode};
use axum::response::{IntoResponse, Response};
use axum::Json;

use crate::etag::{matches_etag, weak_etag};
use crate::extractors::AppPath;
use crate::state::AppState;
use shop_common::dto::ProductResponseDto;
use shop_common::error::AppError;
use shop_db::products as db;

pub async fn get_product(
    State(state): State<AppState>,
    AppPath(slug): AppPath<String>,
    headers: HeaderMap,
) -> Result<Response, AppError> {
    // Load row, mặc định exclude soft deleted (include_deleted = false)
    let row = db::find_by_slug(&state.db, &slug, false)
        .await?
        .ok_or_else(|| AppError::NotFound(
            format!("product '{}' not found", slug)
        ))?;

    let etag = weak_etag(row.updated_at);

    // Check If-None-Match → trả 304 nếu match
    if matches_etag(&headers, &etag) {
        return Ok(StatusCode::NOT_MODIFIED.into_response());
    }

    // Build response với ETag header
    let dto = ProductResponseDto::from(row);
    let mut response = Json(dto).into_response();
    response.headers_mut().insert(
        header::ETAG,
        etag.parse().unwrap(),
    );

    Ok(response)
}

Verify flow end-to-end:

# Request 1 — fetch lần đầu, lấy ETag
curl -i http://localhost:3000/api/v1/products/iphone-15
# < HTTP/1.1 200 OK
# < ETag: W/"1718438400"
# < Content-Type: application/json
# < {"id": 1, "name": "iPhone 15", "slug": "iphone-15", ...}

# Request 2 — kèm If-None-Match → 304 Not Modified
curl -i http://localhost:3000/api/v1/products/iphone-15 \
  -H 'If-None-Match: W/"1718438400"'
# < HTTP/1.1 304 Not Modified
# < (no body)

# Update product → updated_at thay đổi
curl -X PATCH http://localhost:3000/api/v1/products/iphone-15 \
  -H 'Content-Type: application/json' \
  -d '{"stock": 5}'

# Request 3 — kèm ETag cũ → server thấy updated_at mới, trả 200 với ETag mới
curl -i http://localhost:3000/api/v1/products/iphone-15 \
  -H 'If-None-Match: W/"1718438400"'
# < HTTP/1.1 200 OK
# < ETag: W/"1718438500"
# < {"id": 1, ..., "stock": 5}

Trigger Postgres products_updated_at đảm bảo updated_at luôn refresh khi UPDATE chạy — ETag tự động chính xác mà không cần handler set thủ công. Pattern lock cho mọi GET single resource endpoint Shop API: extract updated_at → build weak ETag → check If-None-Match → 304 hoặc 200 + ETag header.

Cập nhật ProductRow trong crates/shop-db/src/products.rs thêm field deleted_at:

// File: crates/shop-db/src/products.rs (extend ProductRow)
use chrono::{DateTime, Utc};
use rust_decimal::Decimal;
use serde_json::Value;

pub struct ProductRow {
    pub id: i64,
    pub name: String,
    pub slug: String,
    pub price: Decimal,
    pub stock: i32,
    pub description: Option<String>,
    pub tags: Vec<String>,
    pub metadata: Value,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,  // ← NEW B62
}

Refactor find_by_slug nhận thêm tham số include_deleted:

// File: crates/shop-db/src/products.rs (refactor find_by_slug)
use sqlx::PgPool;

pub async fn find_by_slug(
    pool: &PgPool,
    slug: &str,
    include_deleted: bool,
) -> Result<Option<ProductRow>, sqlx::Error> {
    if include_deleted {
        sqlx::query_as!(
            ProductRow,
            r#"SELECT id, name, slug, price, stock, description, tags, metadata,
                      created_at, updated_at, deleted_at
               FROM products WHERE slug = $1"#,
            slug
        )
        .fetch_optional(pool)
        .await
    } else {
        sqlx::query_as!(
            ProductRow,
            r#"SELECT id, name, slug, price, stock, description, tags, metadata,
                      created_at, updated_at, deleted_at
               FROM products
               WHERE slug = $1 AND deleted_at IS NULL"#,
            slug
        )
        .fetch_optional(pool)
        .await
    }
}

Thêm 2 function mới: soft_delete_product và restore_product:

// File: crates/shop-db/src/products.rs (NEW functions)

/// Soft delete: set deleted_at = NOW().
/// Trả về số row affected — 0 nghĩa là không tìm thấy
/// hoặc đã soft deleted rồi.
pub async fn soft_delete_product(
    pool: &PgPool,
    slug: &str,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        r#"UPDATE products SET deleted_at = NOW()
           WHERE slug = $1 AND deleted_at IS NULL"#,
        slug
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}

/// Restore: set deleted_at = NULL.
/// Trả về số row affected — 0 nghĩa là không tìm thấy
/// hoặc row chưa bao giờ soft deleted.
pub async fn restore_product(
    pool: &PgPool,
    slug: &str,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        r#"UPDATE products SET deleted_at = NULL
           WHERE slug = $1 AND deleted_at IS NOT NULL"#,
        slug
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}

Update list_products và search_products filter deleted_at IS NULL mặc định (default exclude soft deleted) — admin endpoint sẽ override flag include_deleted=true ở Bước 7.

Refactor handler delete_product trong crates/shop-api/src/routes/products.rs:

// File: crates/shop-api/src/routes/products.rs (refactor delete_product)
use crate::responses::NoContent;

pub async fn delete_product(
    State(state): State<AppState>,
    AppPath(slug): AppPath<String>,
) -> Result<NoContent, AppError> {
    let affected = db::soft_delete_product(&state.db, &slug).await?;

    if affected == 0 {
        return Err(AppError::NotFound(
            format!("product '{}' not found or already deleted", slug)
        ));
    }

    Ok(NoContent)
}

Behavior từ client perspective KHÔNG đổi: DELETE /api/v1/products/{slug} vẫn trả 204 No Content khi thành công, vẫn 404 NotFound khi slug không tồn tại. Khác biệt nằm ở DB: row vẫn còn, chỉ bị flag deleted_at set. Audit log giữ nguyên trace, future analytics query lịch sử order kèm tên product đã "xoá" vẫn hoạt động qua join.

Tạo handler restore — pattern action verb POST đã lock B61:

// File: crates/shop-api/src/routes/products.rs (NEW handler)

pub async fn restore_product(
    State(state): State<AppState>,
    AppPath(slug): AppPath<String>,
) -> Result<Json<ProductResponseDto>, AppError> {
    let affected = db::restore_product(&state.db, &slug).await?;

    if affected == 0 {
        return Err(AppError::NotFound(format!(
            "product '{}' not found or not soft-deleted",
            slug
        )));
    }

    // Reload row đã restore để trả về cho client
    let row = db::find_by_slug(&state.db, &slug, false)
        .await?
        .ok_or_else(|| AppError::Internal(
            "product restored but not findable".into()
        ))?;

    Ok(Json(ProductResponseDto::from(row)))
}

Wire route mới trong routes() function (B61 lock action verb pattern):

// File: crates/shop-api/src/routes/products.rs (extend routes)
use axum::routing::{get, post};
use axum::Router;
use axum::extract::DefaultBodyLimit;

pub fn routes() -> Router<AppState> {
    Router::new()
        .route(
            "/products",
            get(list_products).post(create_product),
        )
        .route(
            "/products/{slug}",
            get(get_product).patch(update_product).delete(delete_product),
        )
        // NEW B62 — action verb pattern lock B61
        .route(
            "/products/{slug}/restore",
            post(restore_product),
        )
        .route(
            "/products/export.ndjson",
            get(export_products_ndjson),
        )
        .route(
            "/products/import.ndjson",
            post(import_products_ndjson)
                .layer(DefaultBodyLimit::max(10 * 1024 * 1024)),
        )
}

Verify flow soft delete + restore:

# Soft delete
curl -X DELETE http://localhost:3000/api/v1/products/iphone-15
# < HTTP/1.1 204 No Content

# GET ngay sau → 404 (default exclude soft deleted)
curl http://localhost:3000/api/v1/products/iphone-15
# < HTTP/1.1 404 Not Found
# < {"error":"not found: product 'iphone-15' not found","code":"NOT_FOUND",...}

# Restore qua action verb POST
curl -X POST http://localhost:3000/api/v1/products/iphone-15/restore
# < HTTP/1.1 200 OK
# < {"id":1,"name":"iPhone 15","slug":"iphone-15",...}

# GET lại → 200 OK, row đã active trở lại
curl http://localhost:3000/api/v1/products/iphone-15
# < HTTP/1.1 200 OK
# < ETag: W/"<new_unix>"
# < {"id":1,...}

# Restore khi không phải soft deleted → 404 (defensive check)
curl -X POST http://localhost:3000/api/v1/products/iphone-15/restore
# < HTTP/1.1 404 Not Found
# < {"error":"not found: product 'iphone-15' not found or not soft-deleted",...}

Endpoint restore chỉ dành cho admin. Hiện tại chưa wire auth middleware (G11+G12 sẽ deep), nhưng route đã sẵn sàng cho admin gate ở B112 — chỉ cần thêm layer require_role("admin") vào sub-router.

Extend ProductSearchQuery trong crates/shop-common/src/dto/product.rs thêm field admin:

// File: crates/shop-common/src/dto/product.rs (extend struct)
pub struct ProductSearchQuery {
    // ... 9 field existing từ B59 + B60
    pub name: Option<String>,
    pub tag: Option<String>,
    pub min_price: Option<Decimal>,
    pub max_price: Option<Decimal>,
    pub sort: String,
    pub page: u32,
    pub per_page: u32,
    pub has_metadata_key: Option<String>,
    pub metadata_contains: Option<Value>,

    // NEW B62 — admin only, default false
    #[serde(default)]
    pub include_deleted: bool,
}

Sync field include_deleted sang ProductFilter tại DB layer và refactor apply_filter nhận thêm tham số:

// File: crates/shop-db/src/products.rs (refactor apply_filter)
fn apply_filter(
    qb: &mut QueryBuilder<'_, Postgres>,
    filter: &ProductFilter,
) {
    // NEW B62 — default exclude soft deleted
    if !filter.include_deleted {
        qb.push(" AND deleted_at IS NULL");
    }

    // ... 6 conditional filter existing (name, min_price, max_price,
    //     tag, has_metadata_key, metadata_contains)
    if let Some(name) = &filter.name {
        qb.push(" AND name ILIKE ").push_bind(format!("%{}%", name));
    }
    // ... (giữ nguyên các filter khác)
}

Pattern enforcement — user-facing endpoint luôn force include_deleted = false bất kể client gửi gì, admin endpoint mới tôn trọng query parameter:

// File: crates/shop-api/src/routes/products.rs (handler list_products user-facing)
pub async fn list_products(
    State(state): State<AppState>,
    AppQuery(mut query): AppQuery<ProductSearchQuery>,
) -> Result<Json<ProductListResponse>, AppError> {
    // User endpoint LUÔN exclude soft deleted, bỏ qua gì client gửi
    query.include_deleted = false;

    let filter = ProductFilter::from(query);
    let (rows, total) = db::search_products(&state.db, filter).await?;
    // ... build response
}

Admin endpoint (preview, sẽ implement đầy đủ B112 sau khi auth):

// File: crates/shop-api/src/routes/admin/products.rs (preview B112)
pub async fn admin_list_products(
    State(state): State<AppState>,
    _admin: AdminUser,  // B112 auth extractor
    AppQuery(query): AppQuery<ProductSearchQuery>,
) -> Result<Json<ProductListResponse>, AppError> {
    // Admin endpoint TÔN TRỌNG query.include_deleted client gửi
    let filter = ProductFilter::from(query);
    let (rows, total) = db::search_products(&state.db, filter).await?;
    // ... build response
}

3 decision lock Shop API:

• User endpoint default exclude — bất kể client gửi ?include_deleted=true, handler force về false. Bảo vệ semantic "user không thấy data đã xoá".
• Admin endpoint respect flag — gate qua extractor AdminUser (B112), client gửi ?include_deleted=true mới được phép thấy soft deleted.
• Restore endpoint admin only — wrap qua sub-router /api/v1/admin/* trong B112, hiện tại public tạm thời để verify pattern.

Tạo helper service ghi audit log trong scope transaction:

// File: crates/shop-db/src/audit.rs (NEW)

use serde_json::Value;
use sqlx::{Postgres, Transaction};

/// Ghi 1 row audit log trong scope transaction.
/// Transaction-scoped để audit + mutation cùng commit hoặc rollback atomic.
pub async fn log_action(
    tx: &mut Transaction<'_, Postgres>,
    table_name: &str,
    row_id: i64,
    action: &str,
    actor_user_id: Option<i64>,
    changes: Option<Value>,
    request_id: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        INSERT INTO audit_logs
            (table_name, row_id, action, actor_user_id, changes, request_id)
        VALUES ($1, $2, $3, $4, $5, $6)
        "#,
        table_name,
        row_id,
        action,
        actor_user_id,
        changes,
        request_id
    )
    .execute(&mut **tx)
    .await?;
    Ok(())
}

Wire module trong crates/shop-db/src/lib.rs:

// File: crates/shop-db/src/lib.rs (extend)
pub mod audit;
pub mod orders;
pub mod payments;
pub mod pool;
pub mod products;

Refactor soft_delete_product wrap transaction + audit:

// File: crates/shop-db/src/products.rs (NEW soft_delete_with_audit)
use crate::audit;
use sqlx::PgPool;

/// Soft delete trong transaction kèm audit log.
/// Pattern lock Shop API: get current → mutation → audit → commit.
pub async fn soft_delete_with_audit(
    pool: &PgPool,
    slug: &str,
    actor_user_id: Option<i64>,
    request_id: Option<&str>,
) -> Result<u64, sqlx::Error> {
    let mut tx = pool.begin().await?;

    // Step 1 — get current state để biết row_id phục vụ audit
    let row = sqlx::query!(
        "SELECT id FROM products WHERE slug = $1 AND deleted_at IS NULL",
        slug
    )
    .fetch_optional(&mut *tx)
    .await?;

    let row = match row {
        Some(r) => r,
        None => {
            tx.rollback().await?;
            return Ok(0);
        }
    };

    // Step 2 — mutation
    let result = sqlx::query!(
        "UPDATE products SET deleted_at = NOW() WHERE id = $1",
        row.id
    )
    .execute(&mut *tx)
    .await?;

    // Step 3 — audit log cùng transaction
    audit::log_action(
        &mut tx,
        "products",
        row.id,
        "delete",
        actor_user_id,
        Some(serde_json::json!({ "deleted_at": "now" })),
        request_id,
    )
    .await?;

    // Step 4 — commit atomic
    tx.commit().await?;
    Ok(result.rows_affected())
}

Handler lấy request_id qua Extension<RequestId> đã lock B39, lấy actor_user_id từ auth context (B112 sẽ deep). Hiện tại placeholder None cho cả 2 chấp nhận:

// File: crates/shop-api/src/routes/products.rs (refactor delete_product
// với audit)
use axum::Extension;
use crate::middleware::RequestId;

pub async fn delete_product(
    State(state): State<AppState>,
    AppPath(slug): AppPath<String>,
    Extension(request_id): Extension<RequestId>,
) -> Result<NoContent, AppError> {
    let affected = db::soft_delete_with_audit(
        &state.db,
        &slug,
        None,  // actor_user_id — B112 sẽ wire từ JWT claims
        Some(request_id.as_str()),
    )
    .await?;

    if affected == 0 {
        return Err(AppError::NotFound(
            format!("product '{}' not found or already deleted", slug)
        ));
    }

    Ok(NoContent)
}

Pattern lock vĩnh viễn Shop API cho mọi mutation (products/orders/users/payments): begin transaction → get current state → mutation → log_action → commit. Nếu bất cứ step nào fail, transaction rollback toàn bộ — audit log không bao giờ phản ánh state không đúng. Template soft_delete_with_audit reuse cho update_with_audit, restore_with_audit, create_with_audit với cùng skeleton 4 bước.

Migration + start service:

sqlx migrate run --source crates/shop-db/migrations
# Applied 20260615160000/migrate add_products_soft_delete
# Applied 20260615160100/migrate create_audit_log

cargo sqlx prepare --workspace
# query data written to .sqlx in the current directory

AUTO_MIGRATE=true cargo run -p shop-api
# shop-api listening on 0.0.0.0:3000

Test full lifecycle product:

# Create
curl -X POST http://localhost:3000/api/v1/products \
  -H 'Content-Type: application/json' \
  -d '{"name":"iPhone 15","slug":"iphone-15","price":"25000000.00","stock":10}'
# 201 Created + Location: /api/v1/products/iphone-15

# GET với ETag
curl -i http://localhost:3000/api/v1/products/iphone-15
# ETag: W/"1718438400"

# Conditional GET → 304
curl -i http://localhost:3000/api/v1/products/iphone-15 \
  -H 'If-None-Match: W/"1718438400"'
# 304 Not Modified

# Soft delete
curl -X DELETE http://localhost:3000/api/v1/products/iphone-15
# 204 No Content

# Verify audit log
docker compose exec postgres psql -U shop -d shop_dev -c \
  "SELECT id, table_name, row_id, action, request_id, created_at
   FROM audit_logs ORDER BY created_at DESC LIMIT 5;"
# id | table_name | row_id | action | request_id        | created_at
# ---+-----------+--------+--------+-------------------+---------------
#  1 | products  |      1 | delete | 550e8400-e29b-... | 2026-06-15 ...

# Restore
curl -X POST http://localhost:3000/api/v1/products/iphone-15/restore
# 200 OK + body ProductResponseDto

# GET lại → 200 OK với ETag mới (trigger updated_at đã refresh)
curl -i http://localhost:3000/api/v1/products/iphone-15
# ETag: W/"1718438500" (khác giá trị cũ)

Suggested commit khi mọi bước verify pass: B62: ETag + soft delete + restore endpoint + audit log table.

• Migration 6 + 7: add_products_soft_delete + create_audit_log.
• deleted_at TIMESTAMPTZ NULLABLE soft delete pattern lock vĩnh viễn cho products/orders/users.
• Partial index WHERE deleted_at IS NULL — chỉ index active row, giảm size 30-50%.
• Trigger Postgres update_updated_at_column auto-refresh updated_at mỗi UPDATE.
• ETag weak helper W/"<unix_timestamp>" + matches_etag function trong crates/shop-api/src/etag.rs.
• GET single resource flow: load row → build ETag → check If-None-Match → 304 hoặc 200 + ETag header.
• POST /products/{slug}/restore endpoint action verb pattern (B61 lock continued).
• include_deleted flag chỉ admin endpoint respect; user endpoint default force false.
• audit_logs table 7 column (id + table_name + row_id + action + actor_user_id + changes JSONB + request_id + created_at) + 3 index.
• log_action helper transaction-scoped — wrap mọi mutation, audit + mutation atomic commit/rollback.
• Service layer pattern lock: get current → mutation → audit → commit; template soft_delete_with_audit reuse cho update/restore/create.
• File path lock: crates/shop-api/src/etag.rs, crates/shop-db/src/audit.rs, migration 6 + 7.
• Foundation cho B63 (Categories Tree CRUD), B65 (orders soft delete), B112 (admin auth gate restore endpoint).

Tự trả lời, đáp án ở cuối:

1. Soft delete với deleted_at TIMESTAMPTZ NULLABLE — partial index WHERE deleted_at IS NULL giải quyết vấn đề gì? So sánh với full index.
2. Trigger Postgres update_updated_at_column so với set updated_at trong handler Rust — pros/cons mỗi cách? Shop API chọn cách nào và lý do.
3. ETag weak W/"<unix>" vs strong "<sha256>" — Shop API chọn weak vì lý do gì? Trade-off false negative ra sao?
4. POST /products/{slug}/restore action verb pattern — tại sao không dùng PATCH /products/{slug} với body {"deleted_at": null}?
5. Audit log nên ghi qua trigger Postgres hay service layer Rust? Shop API quyết định cái nào và lý do cụ thể.

Bài 63: Categories Tree CRUD — Adjacency List + Materialized Path — chi tiết tree structure cho categories: adjacency list (parent_id) vs materialized path (path TEXT), Postgres CTE recursive query, áp Shop API category tree endpoint GET /categories trả nested JSON.