#1071 Cut per-request DB work on osctrl-tls and osctrl-api hot paths Pull request opened by javuto Cut per-request DB work on osctrl-tls and osctrl-api hot paths Several paths that run on every node check-in or every SPA poll were doing far more database work than needed. This PR removes it without changing response shapes. osctrl-tls • DB log sink: batched inserts (

pkg/logging/db.go

). The default

logger.type: db

inserted each status/result line in its own transaction, so a batch of N lines cost N commits. It now uses multi-row INSERTs in a single transaction. A 200-line batch dropped from 87 ms to 1.3 ms on file-backed SQLite; Postgres should gain more, since each commit there is a WAL flush. • If the batch fails, it is rolled back and retried row by row, so one unstorable row (e.g. a NUL byte, which Postgres rejects in

text

) still loses only itself. • Rows get strictly increasing

created_at

at storage precision. Readers order by

created_at

alone, so this keeps batch lines in send order. • Query completion check (

UpdateQueryStatus

). Each node result ran

COUNT(*)

over every node_query row of the query (

status

isn't indexed), which is O(targets²) over a query's life. It now does an existence check (

LIMIT 1

), and the SELECT-then-UPDATE of the node's row is one UPDATE. • Node metadata per log batch (

UpdateMetadataByUUID

). A full-row SELECT plus an UPDATE became a single UPDATE. •

bytes_received

is now incremented in SQL. The old read-modify-write lost bytes when batches overlapped: in a test with 8 concurrent writers, 75 of 600 bytes were recorded. • The update is scoped to the environment the request authenticated against. osctrl-api • Auth middleware no longer re-fetches the user it just loaded. It skips the

last_token_use

write when the IP is unchanged and the last recorded use is under a minute old. • Activity tiles batch: up to 100 full-row lookups per poll replaced by one two-column

IN

query. • `/stats`: per-environment full-row loads of active queries and carves, made only to

len()

them, replaced by one grouped

COUNT

. Behaviour changes •

last_token_use

now has minute resolution; a new IP is still recorded immediately. It's display-only, and nothing authorizes on it. • For a UUID enrolled in two environments, metadata now updates the row in the environment the request authenticated against, not whichever row has the lowest id. •

MetadataRefresh

is removed; its only caller was

UpdateMetadataByUUID

. Not in this PR • Indexes: composite indexes on

osquery_nodes

,

node_queries

,

distributed_queries

, and the log tables would be the next big win. I left them out because

AutoMigrate

creates indexes at startup, and on Postgres that blocks writes to large tables while it runs. • Metadata spoofing: metadata still targets the UUID claimed in the log payload's

hostIdentifier

, not the authenticated node. That's tracked separately as a security fix. Validation •

go build ./...

and

go vet ./...

are clean, and all packages pass

go test ./...

. • New regression tests cover batched inserts, send order, the bad-row fallback, completion edge cases (untargeted node, errored results, wide fan-out), single-statement metadata updates, concurrent byte counting, environment scoping, the batched membership lookup, and grouped counts checked against `GetQueries`/`GetCarves`. • Mutation-checked: with the fix reverted, both the fallback test and the concurrency test fail. •

golangci-lint

reports one pre-existing goimports issue in

pkg/queries/events_test.go

, which this PR doesn't touch. jmpsec/osctrl