coder · ethanndickson · Jul 3, 2025 · Jul 3, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1510,6 +1510,16 @@ func (q *querier) DeleteOAuth2ProviderAppTokensByAppAndUserID(ctx context.Contex
 	return q.db.DeleteOAuth2ProviderAppTokensByAppAndUserID(ctx, arg)
 }
 
+func (q *querier) DeleteOldAuditLogConnectionEvents(ctx context.Context, threshold database.DeleteOldAuditLogConnectionEventsParams) error {
+	// `ResourceSystem` is deprecated, but it doesn't make sense to add
+	// `policy.ActionDelete` to `ResourceAuditLog`, since this is the one and
+	// only time we'll be deleting from the audit log.
+	if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
+		return err
+	}
+	return q.db.DeleteOldAuditLogConnectionEvents(ctx, threshold)
+}
+
 func (q *querier) DeleteOldNotificationMessages(ctx context.Context) error {
 	if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceNotificationMessage); err != nil {
 		return err

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -327,6 +327,10 @@ func (s *MethodTestSuite) TestAuditLogs() {
 			LimitOpt: 10,
 		}, emptyPreparedAuthorized{}).Asserts(rbac.ResourceAuditLog, policy.ActionRead)
 	}))
+	s.Run("DeleteOldAuditLogConnectionEvents", s.Subtest(func(db database.Store, check *expects) {
+		_ = dbgen.AuditLog(s.T(), db, database.AuditLog{})
+		check.Args(database.DeleteOldAuditLogConnectionEventsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
+	}))
 }
 
 func (s *MethodTestSuite) TestConnectionLogs() {

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -2188,6 +2188,27 @@ func (q *FakeQuerier) DeleteOAuth2ProviderAppTokensByAppAndUserID(_ context.Cont
 	return nil
 }
 
+func (q *FakeQuerier) DeleteOldAuditLogConnectionEvents(ctx context.Context, params database.DeleteOldAuditLogConnectionEventsParams) error {
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+
+	deleted := 0
+	newAuditLogs := make([]database.AuditLog, 0, len(q.auditLogs))
+	for _, auditLog := range q.auditLogs {
+		isConnectionEvent := auditLog.Action == database.AuditActionConnect ||
+			auditLog.Action == database.AuditActionDisconnect ||
+			auditLog.Action == database.AuditActionOpen ||
+			auditLog.Action == database.AuditActionClose
+		if isConnectionEvent && auditLog.Time.Before(params.BeforeTime) && deleted < int(params.LimitCount) {
+			deleted++
+			continue
+		}
+		newAuditLogs = append(newAuditLogs, auditLog)
+	}
+	q.auditLogs = newAuditLogs
+	return nil
+}
+
 func (*FakeQuerier) DeleteOldNotificationMessages(_ context.Context) error {
 	return nil
 }

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dbpurge/dbpurge.go b/coderd/database/dbpurge/dbpurge.go
@@ -18,6 +18,11 @@ import (
 const (
 	delay          = 10 * time.Minute
 	maxAgentLogAge = 7 * 24 * time.Hour
+	// Connection events are now inserted into the `connection_logs` table.
+	// We'll slowly remove old connection events from the `audit_logs` table,
+	// but we won't touch the `connection_logs` table.
+	maxAuditLogConnectionEventAge    = 90 * 24 * time.Hour // 90 days
+	auditLogConnectionEventBatchSize = 1000
 )
 
 // New creates a new periodically purging database instance.
@@ -63,6 +68,14 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, clk quartz.
 				return xerrors.Errorf("failed to delete old notification messages: %w", err)
 			}
 
+			deleteOldAuditLogConnectionEventsBefore := start.Add(-maxAuditLogConnectionEventAge)
+			if err := tx.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
+				BeforeTime: deleteOldAuditLogConnectionEventsBefore,
+				LimitCount: auditLogConnectionEventBatchSize,
+			}); err != nil {
+				return xerrors.Errorf("failed to delete old audit log connection events: %w", err)
+			}
+
 			logger.Debug(ctx, "purged old database entries", slog.F("duration", clk.Since(start)))
 
 			return nil

diff --git a/coderd/database/dbpurge/dbpurge_test.go b/coderd/database/dbpurge/dbpurge_test.go
@@ -490,3 +490,148 @@ func containsProvisionerDaemon(daemons []database.ProvisionerDaemon, name string
 		return d.Name == name
 	})
 }
+
+//nolint:paralleltest // It uses LockIDDBPurge.
+func TestDeleteOldAuditLogConnectionEvents(t *testing.T) {
+	ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
+	defer cancel()
+
+	clk := quartz.NewMock(t)
+	now := dbtime.Now()
+	afterThreshold := now.Add(-91 * 24 * time.Hour)       // 91 days ago (older than 90 day threshold)
+	beforeThreshold := now.Add(-30 * 24 * time.Hour)      // 30 days ago (newer than 90 day threshold)
+	closeBeforeThreshold := now.Add(-89 * 24 * time.Hour) // 89 days ago
+	clk.Set(now).MustWait(ctx)
+
+	db, _ := dbtestutil.NewDB(t, dbtestutil.WithDumpOnFailure())
+	logger := slogtest.Make(t, &slogtest.Options{IgnoreErrors: true})
+	user := dbgen.User(t, db, database.User{})
+	org := dbgen.Organization(t, db, database.Organization{})
+
+	oldConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           afterThreshold,
+		Action:         database.AuditActionConnect,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	oldDisconnectLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           afterThreshold,
+		Action:         database.AuditActionDisconnect,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	oldOpenLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           afterThreshold,
+		Action:         database.AuditActionOpen,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	oldCloseLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           afterThreshold,
+		Action:         database.AuditActionClose,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	recentConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           beforeThreshold,
+		Action:         database.AuditActionConnect,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	oldNonConnectionLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           afterThreshold,
+		Action:         database.AuditActionCreate,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	nearThresholdConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+		Time:           closeBeforeThreshold,
+		Action:         database.AuditActionConnect,
+		ResourceType:   database.ResourceTypeWorkspace,
+	})
+
+	// Run the purge
+	done := awaitDoTick(ctx, t, clk)
+	closer := dbpurge.New(ctx, logger, db, clk)
+	defer closer.Close()
+	// Wait for tick
+	testutil.TryReceive(ctx, t, done)
+
+	// Verify results by querying all audit logs
+	logs, err := db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
+	require.NoError(t, err)
+
+	// Extract log IDs for comparison
+	logIDs := make([]uuid.UUID, len(logs))
+	for i, log := range logs {
+		logIDs[i] = log.AuditLog.ID
+	}
+
+	require.NotContains(t, logIDs, oldConnectLog.ID, "old connect log should be deleted")
+	require.NotContains(t, logIDs, oldDisconnectLog.ID, "old disconnect log should be deleted")
+	require.NotContains(t, logIDs, oldOpenLog.ID, "old open log should be deleted")
+	require.NotContains(t, logIDs, oldCloseLog.ID, "old close log should be deleted")
+	require.Contains(t, logIDs, recentConnectLog.ID, "recent connect log should be kept")
+	require.Contains(t, logIDs, nearThresholdConnectLog.ID, "near threshold connect log should be kept")
+	require.Contains(t, logIDs, oldNonConnectionLog.ID, "old non-connection log should be kept")
+}
+
+func TestDeleteOldAuditLogConnectionEventsLimit(t *testing.T) {
+	t.Parallel()
+
+	ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
+	defer cancel()
+
+	db, _ := dbtestutil.NewDB(t, dbtestutil.WithDumpOnFailure())
+	user := dbgen.User(t, db, database.User{})
+	org := dbgen.Organization(t, db, database.Organization{})
+
+	now := dbtime.Now()
+	threshold := now.Add(-90 * 24 * time.Hour)
+
+	for i := 0; i < 5; i++ {
+		dbgen.AuditLog(t, db, database.AuditLog{
+			UserID:         user.ID,
+			OrganizationID: org.ID,
+			Time:           threshold.Add(-time.Duration(i+1) * time.Hour),
+			Action:         database.AuditActionConnect,
+			ResourceType:   database.ResourceTypeWorkspace,
+		})
+	}
+
+	err := db.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
+		BeforeTime: threshold,
+		LimitCount: 1,
+	})
+	require.NoError(t, err)
+
+	logs, err := db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
+	require.NoError(t, err)
+
+	require.Len(t, logs, 4)
+
+	err = db.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
+		BeforeTime: threshold,
+		LimitCount: 100,
+	})
+	require.NoError(t, err)
+
+	logs, err = db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
+	require.NoError(t, err)
+
+	require.Len(t, logs, 0)
+}
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/auditlogs.sql b/coderd/database/queries/auditlogs.sql
@@ -156,3 +156,19 @@ INSERT INTO
     )
 VALUES
 	($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15) RETURNING *;
+
+-- name: DeleteOldAuditLogConnectionEvents :exec
+DELETE FROM audit_logs
+WHERE id IN (
+    SELECT id FROM audit_logs
+    WHERE
+        (
+            action = 'connect'
+            OR action = 'disconnect'
+            OR action = 'open'
+            OR action = 'close'
+        )
+        AND "time" < @before_time::timestamp with time zone
+    ORDER BY "time" ASC
+    LIMIT @limit_count
+);