Skip to content

Commit 9ebcca5

Browse files
mafredrimafredri
authored
feat(coderd/database/dbpurge): add retention for connection logs (#21022)
Add `DeleteOldConnectionLogs` query and integrate it into the `dbpurge` routine. Retention is controlled by `--retention-connection-logs` flag. Disabled (0) by default. Depends on #21021 Updates #20743
1 parent 56e7858 commit 9ebcca5

File tree

9 files changed

+213
-2
lines changed

9 files changed

+213
-2
lines changed

‎coderd/database/dbauthz/dbauthz.go‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1749,6 +1749,13 @@ func (q *querier) DeleteOldAuditLogConnectionEvents(ctx context.Context, thresho
17491749
return q.db.DeleteOldAuditLogConnectionEvents(ctx, threshold)
17501750
}
17511751

1752+
func (q *querier) DeleteOldConnectionLogs(ctx context.Context, arg database.DeleteOldConnectionLogsParams) (int64, error) {
1753+
if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
1754+
return 0, err
1755+
}
1756+
return q.db.DeleteOldConnectionLogs(ctx, arg)
1757+
}
1758+
17521759
func (q *querier) DeleteOldNotificationMessages(ctx context.Context) error {
17531760
if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceNotificationMessage); err != nil {
17541761
return err

‎coderd/database/dbauthz/dbauthz_test.go‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -355,6 +355,10 @@ func (s *MethodTestSuite) TestConnectionLogs() {
355355
dbm.EXPECT().CountConnectionLogs(gomock.Any(), database.CountConnectionLogsParams{}).Return(int64(0), nil).AnyTimes()
356356
check.Args(database.CountConnectionLogsParams{}, emptyPreparedAuthorized{}).Asserts(rbac.ResourceConnectionLog, policy.ActionRead)
357357
}))
358+
s.Run("DeleteOldConnectionLogs", s.Mocked(func(dbm *dbmock.MockStore, _ *gofakeit.Faker, check *expects) {
359+
dbm.EXPECT().DeleteOldConnectionLogs(gomock.Any(), database.DeleteOldConnectionLogsParams{}).Return(int64(0), nil).AnyTimes()
360+
check.Args(database.DeleteOldConnectionLogsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
361+
}))
358362
}
359363

360364
func (s *MethodTestSuite) TestFile() {

‎coderd/database/dbmetrics/querymetrics.go‎

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go‎

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbpurge/dbpurge.go‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,10 +21,12 @@ const (
2121
delay = 10 * time.Minute
2222
maxAgentLogAge = 7 * 24 * time.Hour
2323
// Connection events are now inserted into the `connection_logs` table.
24-
// We'll slowly remove old connection events from the `audit_logs` table,
25-
// but we won't touch the `connection_logs` table.
24+
// We'll slowly remove old connection events from the `audit_logs` table.
25+
// The `connection_logs` table is purged based on the configured retention.
2626
maxAuditLogConnectionEventAge = 90 * 24 * time.Hour // 90 days
2727
auditLogConnectionEventBatchSize = 1000
28+
// Batch size for connection log deletion.
29+
connectionLogsBatchSize = 10000
2830
// Telemetry heartbeats are used to deduplicate events across replicas. We
2931
// don't need to persist heartbeat rows for longer than 24 hours, as they
3032
// are only used for deduplication across replicas. The time needs to be
@@ -111,9 +113,23 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, vals *coder
111113
return xerrors.Errorf("failed to delete old aibridge records: %w", err)
112114
}
113115

116+
var purgedConnectionLogs int64
117+
connectionLogsRetention := vals.Retention.ConnectionLogs.Value()
118+
if connectionLogsRetention > 0 {
119+
deleteConnectionLogsBefore := start.Add(-connectionLogsRetention)
120+
purgedConnectionLogs, err = tx.DeleteOldConnectionLogs(ctx, database.DeleteOldConnectionLogsParams{
121+
BeforeTime: deleteConnectionLogsBefore,
122+
LimitCount: connectionLogsBatchSize,
123+
})
124+
if err != nil {
125+
return xerrors.Errorf("failed to delete old connection logs: %w", err)
126+
}
127+
}
128+
114129
logger.Debug(ctx, "purged old database entries",
115130
slog.F("expired_api_keys", expiredAPIKeys),
116131
slog.F("aibridge_records", purgedAIBridgeRecords),
132+
slog.F("connection_logs", purgedConnectionLogs),
117133
slog.F("duration", clk.Since(start)),
118134
)
119135

‎coderd/database/dbpurge/dbpurge_test.go‎

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -759,6 +759,129 @@ func TestDeleteOldTelemetryHeartbeats(t *testing.T) {
759759
}, testutil.WaitShort, testutil.IntervalFast, "it should delete old telemetry heartbeats")
760760
}
761761

762+
func TestDeleteOldConnectionLogs(t *testing.T) {
763+
t.Parallel()
764+
765+
now := time.Date(2025, 1, 15, 7, 30, 0, 0, time.UTC)
766+
retentionPeriod := 30 * 24 * time.Hour
767+
afterThreshold := now.Add(-retentionPeriod).Add(-24 * time.Hour) // 31 days ago (older than threshold)
768+
beforeThreshold := now.Add(-15 * 24 * time.Hour) // 15 days ago (newer than threshold)
769+
770+
testCases := []struct {
771+
name string
772+
retentionConfig codersdk.RetentionConfig
773+
oldLogTime time.Time
774+
recentLogTime *time.Time // nil means no recent log created
775+
expectOldDeleted bool
776+
expectedLogsRemaining int
777+
}{
778+
{
779+
name: "RetentionEnabled",
780+
retentionConfig: codersdk.RetentionConfig{
781+
ConnectionLogs: serpent.Duration(retentionPeriod),
782+
},
783+
oldLogTime: afterThreshold,
784+
recentLogTime: &beforeThreshold,
785+
expectOldDeleted: true,
786+
expectedLogsRemaining: 1, // only recent log remains
787+
},
788+
{
789+
name: "RetentionDisabled",
790+
retentionConfig: codersdk.RetentionConfig{
791+
ConnectionLogs: serpent.Duration(0),
792+
},
793+
oldLogTime: now.Add(-365 * 24 * time.Hour), // 1 year ago
794+
recentLogTime: nil,
795+
expectOldDeleted: false,
796+
expectedLogsRemaining: 1, // old log is kept
797+
},
798+
}
799+
800+
for _, tc := range testCases {
801+
t.Run(tc.name, func(t *testing.T) {
802+
t.Parallel()
803+
804+
ctx := testutil.Context(t, testutil.WaitShort)
805+
clk := quartz.NewMock(t)
806+
clk.Set(now).MustWait(ctx)
807+
808+
db, _ := dbtestutil.NewDB(t, dbtestutil.WithDumpOnFailure())
809+
logger := slogtest.Make(t, &slogtest.Options{IgnoreErrors: true})
810+
811+
// Setup test fixtures.
812+
user := dbgen.User(t, db, database.User{})
813+
org := dbgen.Organization(t, db, database.Organization{})
814+
_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{UserID: user.ID, OrganizationID: org.ID})
815+
tv := dbgen.TemplateVersion(t, db, database.TemplateVersion{OrganizationID: org.ID, CreatedBy: user.ID})
816+
tmpl := dbgen.Template(t, db, database.Template{OrganizationID: org.ID, ActiveVersionID: tv.ID, CreatedBy: user.ID})
817+
workspace := dbgen.Workspace(t, db, database.WorkspaceTable{
818+
OwnerID: user.ID,
819+
OrganizationID: org.ID,
820+
TemplateID: tmpl.ID,
821+
})
822+
823+
// Create old connection log.
824+
oldLog := dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
825+
ID: uuid.New(),
826+
Time: tc.oldLogTime,
827+
OrganizationID: org.ID,
828+
WorkspaceOwnerID: user.ID,
829+
WorkspaceID: workspace.ID,
830+
WorkspaceName: workspace.Name,
831+
AgentName: "agent1",
832+
Type: database.ConnectionTypeSsh,
833+
ConnectionStatus: database.ConnectionStatusConnected,
834+
})
835+
836+
// Create recent connection log if specified.
837+
var recentLog database.ConnectionLog
838+
if tc.recentLogTime != nil {
839+
recentLog = dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
840+
ID: uuid.New(),
841+
Time: *tc.recentLogTime,
842+
OrganizationID: org.ID,
843+
WorkspaceOwnerID: user.ID,
844+
WorkspaceID: workspace.ID,
845+
WorkspaceName: workspace.Name,
846+
AgentName: "agent2",
847+
Type: database.ConnectionTypeSsh,
848+
ConnectionStatus: database.ConnectionStatusConnected,
849+
})
850+
}
851+
852+
// Run the purge.
853+
done := awaitDoTick(ctx, t, clk)
854+
closer := dbpurge.New(ctx, logger, db, &codersdk.DeploymentValues{
855+
Retention: tc.retentionConfig,
856+
}, clk)
857+
defer closer.Close()
858+
testutil.TryReceive(ctx, t, done)
859+
860+
// Verify results.
861+
logs, err := db.GetConnectionLogsOffset(ctx, database.GetConnectionLogsOffsetParams{
862+
LimitOpt: 100,
863+
})
864+
require.NoError(t, err)
865+
require.Len(t, logs, tc.expectedLogsRemaining, "unexpected number of logs remaining")
866+
867+
logIDs := make([]uuid.UUID, len(logs))
868+
for i, log := range logs {
869+
logIDs[i] = log.ConnectionLog.ID
870+
}
871+
872+
if tc.expectOldDeleted {
873+
require.NotContains(t, logIDs, oldLog.ID, "old connection log should be deleted")
874+
} else {
875+
require.Contains(t, logIDs, oldLog.ID, "old connection log should NOT be deleted")
876+
}
877+
878+
if tc.recentLogTime != nil {
879+
require.Contains(t, logIDs, recentLog.ID, "recent connection log should be kept")
880+
}
881+
})
882+
}
883+
}
884+
762885
func TestDeleteOldAIBridgeRecords(t *testing.T) {
763886
t.Parallel()
764887

‎coderd/database/querier.go‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go‎

Lines changed: 26 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/connectionlogs.sql‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -239,6 +239,18 @@ WHERE
239239
-- @authorize_filter
240240
;
241241

242+
-- name: DeleteOldConnectionLogs :execrows
243+
WITH old_logs AS (
244+
SELECT id
245+
FROM connection_logs
246+
WHERE connect_time < @before_time::timestamp with time zone
247+
ORDER BY connect_time ASC
248+
LIMIT @limit_count
249+
)
250+
DELETE FROM connection_logs
251+
USING old_logs
252+
WHERE connection_logs.id = old_logs.id;
253+
242254
-- name: UpsertConnectionLog :one
243255
INSERT INTO connection_logs (
244256
id,

0 commit comments

Comments
 (0)