[PR #415] [MERGED] fix: ensure indexes on audit_logs table #823

New Issue

OVERLORD · 2025-10-09T16:57:55+03:00

OVERLORD commented

2025-10-09 16:57:55 +03:00

📋 Pull Request Information

Original PR: https://github.com/pocket-id/pocket-id/pull/415
Author: @ItalyPaleAle
Created: 4/4/2025
Status: ✅ Merged
Merged: 4/4/2025
Merged by: @kmendell

Base: main ← Head: audit-logs-indexes

📝 Commits (4)

946243a fix: ensure indexes on audit_logs table
c88e42d Remove unnecessary indexes
0a4d0d2 Merge branch 'main' into audit-logs-indexes
99f9d1a Merge branch 'main' into audit-logs-indexes

📊 Changes

9 files changed (+63 additions, -34 deletions)

View changed files

📝 backend/internal/controller/audit_log_controller.go (+3 -3)
📝 backend/internal/model/audit_log.go (+9 -6)
📝 backend/internal/model/oidc.go (+7 -4)
📝 backend/internal/model/webauthn.go (+7 -5)
📝 backend/internal/service/audit_log_service.go (+25 -16)
➕ backend/resources/migrations/postgres/20250403082322_audit_logs_indexes.down.sql (+3 -0)
➕ backend/resources/migrations/postgres/20250403082322_audit_logs_indexes.up.sql (+3 -0)
➕ backend/resources/migrations/sqlite/20250403082322_audit_logs_indexes.down.sql (+3 -0)
➕ backend/resources/migrations/sqlite/20250403082322_audit_logs_indexes.up.sql (+3 -0)

📄 Description

The table had complex queries that in all cases were performing full-table scans, and in some cases were parsing each row's data as JSON. This ensures indexes are present on all columns that are used for querying, including on the clientName JSON property in the data column

Also added passing context to the audit log methods which allows interrupting the long-running aggregate queries if the request is canceled (that is a small spin-off from #392 )

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/pocket-id/pocket-id/pull/415 **Author:** [@ItalyPaleAle](https://github.com/ItalyPaleAle) **Created:** 4/4/2025 **Status:** ✅ Merged **Merged:** 4/4/2025 **Merged by:** [@kmendell](https://github.com/kmendell) **Base:** `main` ← **Head:** `audit-logs-indexes` --- ### 📝 Commits (4) - [`946243a`](https://github.com/pocket-id/pocket-id/commit/946243a2d4dc68bb10057904cf7630711061c4e0) fix: ensure indexes on audit_logs table - [`c88e42d`](https://github.com/pocket-id/pocket-id/commit/c88e42dd124cdf238e8d11e6986f8b9df8ad7a23) Remove unnecessary indexes - [`0a4d0d2`](https://github.com/pocket-id/pocket-id/commit/0a4d0d286c4d752cb999ad1a787231d42d0fa0cc) Merge branch 'main' into audit-logs-indexes - [`99f9d1a`](https://github.com/pocket-id/pocket-id/commit/99f9d1a54897da7a131086b059d4173cfa5427cf) Merge branch 'main' into audit-logs-indexes ### 📊 Changes **9 files changed** (+63 additions, -34 deletions) <details> <summary>View changed files</summary> 📝 `backend/internal/controller/audit_log_controller.go` (+3 -3) 📝 `backend/internal/model/audit_log.go` (+9 -6) 📝 `backend/internal/model/oidc.go` (+7 -4) 📝 `backend/internal/model/webauthn.go` (+7 -5) 📝 `backend/internal/service/audit_log_service.go` (+25 -16) ➕ `backend/resources/migrations/postgres/20250403082322_audit_logs_indexes.down.sql` (+3 -0) ➕ `backend/resources/migrations/postgres/20250403082322_audit_logs_indexes.up.sql` (+3 -0) ➕ `backend/resources/migrations/sqlite/20250403082322_audit_logs_indexes.down.sql` (+3 -0) ➕ `backend/resources/migrations/sqlite/20250403082322_audit_logs_indexes.up.sql` (+3 -0) </details> ### 📄 Description The table had complex queries that in all cases were performing full-table scans, and in some cases were parsing each row's data as JSON. This ensures indexes are present on all columns that are used for querying, including on the `clientName` JSON property in the data column Also added passing context to the audit log methods which allows interrupting the long-running aggregate queries if the request is canceled (that is a small spin-off from #392 ) --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

OVERLORD added the pull-request label 2025-10-09 16:57:55 +03:00

OVERLORD closed this issue

2025-10-09 16:57:56 +03:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/pocket-id-pocket-id-2#823