SQL Server · CrudLabDB

Activity log

Every action writes one row to activity_log inside the same transaction, so a failed action leaves no line.

WhenActionArticleDetail
28 Oct 2026 21:26:40 UPDATE ART-1025 Edited
28 Oct 2026 21:26:26 CREATE ART-1025 Created and published: ฟหกด
26 Oct 2026 20:21:56 SEED many rows 24 demo articles created
SQL behind this page

Captured by a Hibernate StatementInspector and by the JdbcTemplate code. ? marks a parameter: values are sent separately, which keeps SQL injection out.

Loading this page
1
SELECT
  COUNT(*)                                                                AS total,
  SUM(CASE WHEN deleted = 0 AND archived = 0 THEN 1 ELSE 0 END)           AS active,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'DRAFT' THEN 1 ELSE 0 END)       AS drafts,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'PUBLISHED' THEN 1 ELSE 0 END)   AS published,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'UNPUBLISHED' THEN 1 ELSE 0 END) AS unpublished,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND pinned = 1 THEN 1 ELSE 0 END)    AS pinned,
  SUM(CASE WHEN deleted = 0 AND favourite = 1 THEN 1 ELSE 0 END)                  AS favourites,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND is_read = 0 THEN 1 ELSE 0 END)   AS unread,
  SUM(CASE WHEN deleted = 0 AND locked = 1 THEN 1 ELSE 0 END)                     AS locked,
  SUM(CASE WHEN deleted = 0 AND archived = 1 THEN 1 ELSE 0 END)                   AS archived,
  SUM(CASE WHEN deleted = 1 THEN 1 ELSE 0 END)                                    AS trash
FROM article
2
select top (?) al1_0.id,al1_0.action,al1_0.article_code,al1_0.article_id,al1_0.created_at,al1_0.detail from activity_log al1_0 order by al1_0.id desc