coder · mafredri · Mar 20, 2025 · Mar 20, 2025 · Mar 20, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -4615,9 +4615,9 @@ func (q *querier) UpsertWorkspaceAgentPortShare(ctx context.Context, arg databas
 	return q.db.UpsertWorkspaceAgentPortShare(ctx, arg)
 }
 
-func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
+func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
 	if err := q.authorizeContext(ctx, policy.ActionUpdate, rbac.ResourceSystem); err != nil {
-		return time.Time{}, err
+		return false, err
 	}
 	return q.db.UpsertWorkspaceAppAuditSession(ctx, arg)
 }

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -12283,10 +12283,10 @@ func (q *FakeQuerier) UpsertWorkspaceAgentPortShare(_ context.Context, arg datab
 	return psl, nil
 }
 
-func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
+func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
 	err := validateDatabaseType(arg)
 	if err != nil {
-		return time.Time{}, err
+		return false, err
 	}
 
 	q.mutex.Lock()
@@ -12320,10 +12320,11 @@ func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg data
 
 		q.workspaceAppAuditSessions[i].UpdatedAt = arg.UpdatedAt
 		if !fresh {
+			q.workspaceAppAuditSessions[i].ID = arg.ID
 			q.workspaceAppAuditSessions[i].StartedAt = arg.StartedAt
-			return arg.StartedAt, nil
+			return true, nil
 		}
-		return s.StartedAt, nil
+		return false, nil
 	}
 
 	q.workspaceAppAuditSessions = append(q.workspaceAppAuditSessions, database.WorkspaceAppAuditSession{
@@ -12337,7 +12338,7 @@ func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg data
 		StartedAt:  arg.StartedAt,
 		UpdatedAt:  arg.UpdatedAt,
 	})
-	return arg.StartedAt, nil
+	return true, nil
 }
 
 func (q *FakeQuerier) GetAuthorizedTemplates(ctx context.Context, arg database.GetTemplatesWithFilterParams, prepared rbac.PreparedAuthorized) ([]database.Template, error) {

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/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000302_fix_app_audit_session_race.down.sql b/coderd/database/migrations/000302_fix_app_audit_session_race.down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE workspace_app_audit_sessions
+	DROP COLUMN id;
diff --git a/coderd/database/migrations/000302_fix_app_audit_session_race.up.sql b/coderd/database/migrations/000302_fix_app_audit_session_race.up.sql
@@ -0,0 +1,5 @@
+-- Add column with default to fix existing rows.
+ALTER TABLE workspace_app_audit_sessions
+	ADD COLUMN id UUID PRIMARY KEY DEFAULT gen_random_uuid();
+ALTER TABLE workspace_app_audit_sessions
+	ALTER COLUMN id DROP DEFAULT;
diff --git a/coderd/database/models.go b/coderd/database/models.go
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/workspaceappaudit.sql b/coderd/database/queries/workspaceappaudit.sql
@@ -1,9 +1,11 @@
 -- name: UpsertWorkspaceAppAuditSession :one
 --
--- Insert a new workspace app audit session or update an existing one, if
--- started_at is updated, it means the session has been restarted.
+-- The returned boolean, new_or_stale, can be used to deduce if a new session
+-- was started. This means that a new row was inserted (no previous session) or
+-- the updated_at is older than stale interval.
 INSERT INTO
 	workspace_app_audit_sessions (
+		id,
 		agent_id,
 		app_id,
 		user_id,
@@ -24,18 +26,25 @@ VALUES
 		$6,
 		$7,
 		$8,
-		$9
+		$9,
+		$10
 	)
 ON CONFLICT
 	(agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code)
 DO
 	UPDATE
 	SET
+		-- ID is used to know if session was reset on upsert.
+		id = CASE
+			WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint || ' ms')::interval
+			THEN workspace_app_audit_sessions.id
+			ELSE EXCLUDED.id
+		END,
 		started_at = CASE
 			WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint || ' ms')::interval
 			THEN workspace_app_audit_sessions.started_at
 			ELSE EXCLUDED.started_at
 		END,
 		updated_at = EXCLUDED.updated_at
 RETURNING
-	started_at;
+	id = $1 AS new_or_stale;
diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go
diff --git a/coderd/workspaceapps/db.go b/coderd/workspaceapps/db.go
@@ -447,16 +447,17 @@ func (p *DBTokenProvider) auditInitRequest(ctx context.Context, w http.ResponseW
 			slog.F("status_code", statusCode),
 		)
 
-		var startedAt time.Time
+		var newOrStale bool
 		err := p.Database.InTx(func(tx database.Store) (err error) {
 			// nolint:gocritic // System context is needed to write audit sessions.
 			dangerousSystemCtx := dbauthz.AsSystemRestricted(ctx)
 
-			startedAt, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
+			newOrStale, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
 				// Config.
 				StaleIntervalMS: p.WorkspaceAppAuditSessionTimeout.Milliseconds(),
 
 				// Data.
+				ID:         uuid.New(),
 				AgentID:    aReq.dbReq.Agent.ID,
 				AppID:      aReq.dbReq.App.ID, // Can be unset, in which case uuid.Nil is fine.
 				UserID:     userID,            // Can be unset, in which case uuid.Nil is fine.
@@ -481,9 +482,9 @@ func (p *DBTokenProvider) auditInitRequest(ctx context.Context, w http.ResponseW
 			return
 		}
 
-		if !startedAt.Equal(aReq.time) {
-			// If the unique session wasn't renewed, we don't want to log a new
-			// audit event for it.
+		if !newOrStale {
+			// We either didn't insert a new session, or the session
+			// didn't timeout due to inactivity.
 			return
 		}
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE workspace_app_audit_sessions
		DROP COLUMN id;