The audit suite: auditing a live database

sqlens:audit judges a database that already exists. Where the lint suite reads your migrations before they run, the audit suite reads the live catalog and the server's own settings of one connection, applies the rules that fit the engine and the version that answered, and reports the same three-valued result — pass, fail, undetermined — with the same exit code a pipeline branches on.

Pointing a tool at a database you care about is the moment it most needs to be harmless, and the moment it most needs to say what it is talking about. So this command forces four statements nobody can make on your behalf: which instance it read, what stays ignored, which tenant the report is about, and what the reading role was allowed to see.

Which instance and which tenant are refused rather than guessed, before anything opens a connection. What stays ignored is read from your configuration and checked against the rules that actually exist, because a line that silences nothing looks exactly like a line that is working. And what the role may see cannot be refused at all — you find that out by asking the server — so every gap there is reported instead, one named undetermined at a time.

What a run touches

The audit builds its own named connection from a copy of your configuration; your application's connection, its settings and its open handle are exactly what they were before. That session is sealed read-only, and the seal is verified rather than trusted — it attempts one write inside a savepoint and requires the refusal. It is bounded by its own statement, lock and idle-in-transaction timeouts, and it reads catalog metadata and server variables. No user table is read, nothing is locked, and nothing is written.

The catalog reader is the checkable version of that promise: the complete list of relations it queries on each engine, and the bounds it sets on itself.

The first run

php artisan sqlens:audit --connection=pgsql --level=6

sqlens 0.0.0 — mode=static profile=local strict-tools=off strict-undetermined=off
  level<=6 active-rules=15 hidden-rules=0
  min-severity=off
  roundtrip=off
  categories=all
  tools=none
  connection pgsql: 18.4 (detected)
  instance pgsql (pgsql) db1.internal:5432/shop role=primary
  suppressed=0

TimeZone
  [fail]  PG.L6.TIMEZONE_NOT_UTC  sqlens.lint  level=6  setting

public.orders
  [fail]  PG.L6.PK_NOT_BIGINT  sqlens.lint  level=6  table

summary: 2 fail, 0 undetermined, 0 suppressed
Level gate (<= 6): 2 breaching
Security severity gate (>= off): 0 breaching
by level: 0=0 1=0 2=0 3=0 4=0 5=0 6=2 7=0 8=0 9=0
by severity: info=0 low=0 medium=0 high=0 critical=0
by downtime class: online=0 blocking=0 rewrite=0

These header lines carry the weight of this page:

• instance names the connection, the driver, the server and database that answered, and the role. It is printed only when an instance was actually addressed, so it can never invent a subject.
• connection pgsql: 18.4 (detected) is the version the rules were gated on. An audit reasons from the version the server reported, never from sqlens.assume_server_version — it is talking to the machine these findings will be applied to. When a pin is set, both numbers appear, each marked with where it came from, and a pin that disagrees with the live server is reported.
• active-rules counts the rules this run admitted: what the level, the category scope and the version the server reported let through, minus the ones this instance cannot answer for. Those last ones are not silent either — see Auditing a replica. It is a count of rules, not of objects: it says how much of the rule set was in play, not how much schema was read, and the two degrade for different reasons. What the reading itself could not cover is the skipped line and the undetermined findings underneath it.
• categories is the second filter axis beside the level, printed on every run — all when nothing narrowed it. A run scoped to one category checks a fraction of what an unscoped one does, and a shorter report that did not say so would read as a complete one.
• suppressed is stated even at zero, so a report that hid twelve findings can never read as a clean one.

mode=static is not a placeholder: an audit reads a catalog, it captures no migration, so neither capture mode describes it.

The console line for a finding carries its rule id, its gates and its reason. The full sentence behind it travels in --format=json, together with the finding's documentation URL — one more reason a pipeline should read the JSON rather than scrape the console. What the audit reports about the run rather than about an object is prefixed sqlens.audit, which is how you tell those apart from what a rule said.

Options

Option	What it does
--connection=	The connection to audit. Without it, sqlens.connection; without that, the only supported connection there is. If there is a choice, the run is refused rather than resolved.
--host=	The one read host to address when the connection offers several. Overrides sqlens.host. A host the connection does not offer is refused, never dialed.
--profile=	The environment profile — local, ci or predeploy — which presets that environment's strictness. Overrides SQLENS_PROFILE and sqlens.profile.
--level=0..9	The cumulative strictness level: level n runs every rule at n and below. Defaults to sqlens.level, and wins over the level a profile presets. Security and privacy rules ignore this gate.
--category=	Scope the run to safety, performance, idiom, convention, security or privacy; repeatable and comma-separable. Defaults to sqlens.categories; empty means all. The scope the run actually used is printed in the header, and one that matches no rule is reported as undetermined, never a silent pass.
--min-severity=	The security severity floor for this run — info, low, medium, high, critical, or none to report without ever blocking. Beats the active profile, which beats sqlens.security.min_severity. It is a RISK dial and not a strictness one: it never moves the level, and the level never moves it.
--format=	The report format — console, json, github, sarif or agent. Defaults to sqlens.reporting.default_format.
--debt=	What to do with the debts this run finds in the CATALOG: check (the default — compare against the committed ledger and report, writing nothing) or record (compare, report, and write the reconciled ledger back). A catalog debt has no migration behind it, so its first_seen is the day this project first looked.
--output=	Write the report to this file instead of STDOUT.
--strict	Treat an undetermined as a failure for this run, whatever sqlens.strict_undetermined says. There is no inverse flag: it can only tighten.
--ignore-baseline	Report every finding, including the ones the baseline accepts. It deliberately does not touch the ignore list.

Warnings and diagnostics go to STDERR, so --format=json > report.json leaves a file with nothing in it but the document a script parses.

The flags sqlens:lint has and this command does not — --path, --file, --pretend, --shadow, --roundtrip — are all about migration files. There is no fast path over a live schema, no pretend mode for a reading that writes nothing anyway, and no rollback to replay.

What is refused before anything connects

Each of these ends the run with the misconfiguration exit code and a message naming what to fix, without a connection being opened:

• A key in config/sqlens.php this package does not know, a value of the wrong type, or one out of range. An unknown key is a key that gets ignored, and ignoring is silent: levl: 3 would produce a green run that checked less and said nothing about it.
• An unknown --format or --category, a --level outside 0..9, an unknown or empty --profile, an --output path that cannot be opened.
• A rule id in sqlens.audit.ignore that no rule answers to (CAP.L0.INVALID_IGNORE_LIST).
• A project that looks multi-tenant and has not said which tenant the report is about (CAP.L0.TENANCY_NOT_DECLARED), or one that declared tenants and named none (CAP.L0.TENANCY_REFERENCE_MISSING).
• More than one supported connection with nothing naming one (CAP.L0.INSTANCE_AMBIGUOUS), more than one read host with nothing pinning one (CAP.L0.AMBIGUOUS_READ_HOSTS), or a pinned host the connection does not offer (CAP.L0.UNOFFERED_HOST).

Each refusal is a finding like any other, so a pipeline reading --format=json sees the id and the sentence rather than only an exit code.

Which instance the report is about

An audit reports about one database, and which one is part of what the report asserts. A primary and its replica disagree about server settings, about lag, and sometimes about the schema itself, so a report that cannot name the server it read is a report about an unknown database — and two such runs that disagree look like drift.

Naming the connection

The connection is resolved in four steps, and the fourth is a refusal:

1. --connection= was given. You said which.
2. sqlens.connection is set. The project said which, once, in writing — the same key the lint and capture paths read.
3. Exactly one supported connection is configured. There was nothing to choose between, and the report names it.
4. Otherwise the run stops, listing the candidates.

More than one supported connection is configured (mysql, pgsql) and none was chosen.
Which instance an audit reads is part of what its report asserts, so SQLens will not
pick one: pass --connection, or set sqlens.connection.

Laravel's own database.default is deliberately not consulted as a fallback here. It is the right default for an application's own queries and the wrong one for an audit, because picking an instance silently puts a claim in the report that nobody made and the reader has no way to notice.

"Supported" means a connection whose driver SQLens has a driver for: pgsql and mysql. A mariadb, sqlite or sqlsrv connection is not a candidate, and naming one explicitly is answered by name rather than half-supported — MariaDB in particular answers Laravel's MySQL driver without sharing its semantics, so reasoning about it would mean reporting MySQL's rules against a server that does not follow them.

Naming the host

A connection whose read block lists more than one host does not name a server, it names a set — and Laravel resolves that set by shuffling it. Two runs of an unchanged project would reach different servers. So the audit refuses to choose:

php artisan sqlens:audit --connection=pgsql --host=replica-2.internal

// config/sqlens.php
'host' => env('SQLENS_HOST'),

--host wins over sqlens.host. A connection that leaves exactly one possibility needs no pin, and neither does one that names no host at all — a Unix socket or a DSN configures no choice, so there is nothing to disambiguate and nothing to check a pin against.

A host the configuration does not offer is refused rather than dialed:

The host "replica-9.internal" was named, and this connection does not configure it — it
offers db1.internal. Connecting to it anyway would audit a server the project never
configured and report it as the project's database, which is worse than not running at all.

Writing a host onto a connection is not the same as reaching it, so after connecting the pin is compared against the server's own answer. A server that names a different address stops the run; a pin by name against an address the server reports is the ordinary case and is reported as an unverified check rather than resolved with a DNS lookup. What happens after connecting covers all three outcomes, and transaction poolers covers the other way the connection you configure is not the server you reach.

Auditing a replica

Whether the instance accepts writes comes from one cheap fact: pg_is_in_recovery() on PostgreSQL, read_only and super_read_only on MySQL. The answer has three values, and the third is not a formality — a managed database or a narrow grant can withhold it, and silence is read as undetermined, never as primary.

The role changes what a finding means, so it is stamped on every finding and not only in the header. A max_connections that is too low on the primary is a production problem; the same number on a replica describes a machine that serves no writes and may be sized differently on purpose.

Rules whose subject is the instance and its write path — the server-settings family — are therefore withheld on anything that is not a confirmed primary. Withheld, not dropped: each one produces its own undetermined notice naming the rule and why it could not answer here, because a check that did not run and a check that passed produce the same silence, and on a replica that silence reads as "the server settings are fine". They are also not counted in active-rules, so a replica audit cannot report the same number of active rules as a primary one over a report that judged less.

An undetermined role withholds exactly as a replica does. A verdict nobody can place is worth as much as one placed on the wrong machine.

Schema rules are unaffected and run normally — under physical replication a replica carries the same schema, and withholding those would make a replica-targeted audit useless without making it any safer. To have the server itself judged, point the audit at the primary.

What the role deliberately says nothing about is replication health: not the lag, not whether a receiver is connected, not who the primary is. Those live on state views this suite does not read.

The ignore list

sqlens.audit.ignore is the valve for a grown database, and it is deliberately not a mute button:

// config/sqlens.php
'audit' => [
    'ignore' => [
        // Silences a rule everywhere. Blunt, and honest about being blunt.
        'rules' => ['PG.L6.PK_UUID_V4'],

        // Silences every rule on a path. For a legacy schema nobody is going to fix.
        'objects' => ['legacy.*'],

        // Silences ONE rule on named paths. Reach for this one.
        'pairs' => [
            ['rule' => 'PG.L5.FK_NO_INDEX', 'objects' => ['public.audit_events']],
        ],
    ],
],

The three forms are listed least precise first, and the last is the one to reach for, because the other two grow by themselves: a rule ignored project-wide keeps ignoring itself on tables added next year, and a schema ignored wholesale hides rules that did not exist when somebody wrote the line.

A path is dot-separated and increasingly specific — schema, schema.table, schema.table.column — and a segment may end in * to match a prefix within that segment only. The glob never crosses a dot, which is what makes a pattern's blast radius readable: public.orders cannot reach public.orders_archive. A pattern covers what is inside what it names (a table's columns) and never what contains it (its schema). If your application has a table prefix, write the name either way: both public.orders and public.wp_orders match.

Findings located in a file

Not every subject lives in a schema. A host-based authentication finding is located in a file, and its object name is a path with a line number:

/etc/postgresql/18/main/pg_hba.conf:117

Dots are not separators there — splitting that one would produce two meaningless halves. So a pattern containing / is matched against the whole path instead, with * free to cross both dots and slashes:

'objects' => [
    // That one line, and only it. Line 118 is a different decision and stays loud.
    '/etc/postgresql/18/main/pg_hba.conf:117',

    // Every line of that one file.
    '/etc/postgresql/18/main/pg_hba.conf:*',
],

The switch is decided by the pattern you write, never guessed from the data: a schema path never contains a slash, so schema patterns behave exactly as described above. Copying the object name straight out of the report therefore does what you expect.

A file pattern matches the whole name, start to end. Unlike a schema path — where naming a table covers its columns — a file path has no such containment, so /etc/postgresql/18/main/pg_hba.conf does not silence line 117 of it, and never reaches pg_hba.conf.bak. Only * is a wildcard; every other character, . included, matches itself.

The table prefix is not applied to file patterns. It exists because your application writes orders where the database stores wp_orders; a file on the server's disk has no second spelling, and inventing one would only widen the pattern.

Nothing disappears. An ignored finding is still produced, still counted and still listed under the source that hid it, and the header says how many and by which form:

  suppressed=12 (objects 3, rules 9)

Two mistakes in an ignore list are caught rather than tolerated, because both are invisible otherwise:

• A rule id that no rule answers to stops the run before it connects. A pattern matching nothing looks exactly like a pattern whose findings are gone, so a typo would leave a rule firing for months while the project believed it was off. The message offers the closest real id and never applies it. An id that names a real rule which does not run in this suite gets its own message naming the suites it does run in — a different mistake, sent to a different place.
• A pattern that matched nothing this run read is reported as a notice (CAP.L0.ORPHANED_IGNORE). That is not an error: a pattern may point at a table that does not exist yet, or at one somebody finally dropped. It is debt, and it is the kind nobody sees, because an orphaned pattern and a working one produce the same report.

An ignore is not a baseline

The two look interchangeable and are not, and the difference is what a number does over time:

	Says	Meant to
An ignore	this rule never applies here	stay
A baseline	this is known, and it is being paid down	shrink

A grown database carries a stock of findings nobody clears in one sprint. If the only tool available is the ignore list, that stock gets written down as decisions — and a project that records its debt as decisions has a burn-down number that never moves, because nothing in the file is supposed to go away.

So set sqlens.baseline.path and let the audit apply it. A baselined finding is hidden and attributed to the baseline, and an entry that matched nothing in the run is always reported as stale, because a baseline nobody prunes stops describing what the project accepts. --ignore-baseline shows everything the baseline is accepting; it deliberately leaves the ignore list standing, and if there was no baseline in force it says so rather than letting an unchanged report read as "nothing was hidden".

What a stale entry costs is the one part that is yours to decide, with sqlens.baseline.stale:

sqlens.baseline.stale	A stale entry is	Exit code
report (default)	named in the report; the verdict is untouched	whatever the findings say
error	named, and the baseline is treated as misconfigured	2, which beats every other code

report is the default because a fresh baseline on a moving codebase would otherwise fail constantly. Reach for error once the file is meant to be kept tight — and note that 2 beats a gate breach on purpose: a baseline nobody pruned is a broken instruction file, not a database that got worse, so it must not be able to hide behind the findings of the run that revealed it. The key means the same thing in sqlens:lint, which reads the same file from the same key.

One honest limit: sqlens:baseline freezes the lint suite's findings. The audit reads the same file, from the same key, in the same format — see the public contracts for that format — but nothing writes catalog findings into it for you.

Saying which tenant

"Audit the database" stops being one statement the moment there is more than one tenant database. A report produced from whichever connection happened to be default describes one tenant, and it is indistinguishable from a report about the application: same header, same findings, same exit code. The reader then draws a conclusion about their system from a statement about one of their customers.

SQLens does not resolve that quietly. When it sees signs of a tenant setup, it stops and asks. The signals are narrow, and every one of them is a fact somebody deliberately wrote into the configuration — none of them inspects a database or guesses from a table name:

• Sibling connections on one driver sharing a name prefix. tenant_eu beside tenant_us is a tenancy shape; pgsql beside mysql is an ordinary application with two engines, and the driver check is what keeps the two apart. A connection whose name starts with another configured connection's name is read as that connection extended, not as its sibling.
• A configured tenant database prefix — tenancy.database.prefix, the key the most widely deployed tenancy package writes. A project that set one has already told us, and this catches the setup the connection-shaped signal cannot: switching one connection's database per tenant leaves a single entry under database.connections.
• A known tenancy package in composer.json — stancl/tenancy, spatie/laravel-multitenancy, tenancy/tenancy or hyn/multi-tenant. The require block only: a tenancy package pulled in to run a test suite does not shape the connections an audit addresses.

Answer with two keys:

// config/sqlens.php
'audit' => [
    'tenancy' => [
        'mode' => 'explicit',
        'reference' => 'tenant_eu',
    ],
],

The report then names its subject, in the header, on every run:

  tenant=tenant_eu (this report describes that tenant only)

Three things are worth knowing before you write those two lines.

The reference is a label, not a selector. It states which tenant the report is about; which database is read is still decided by --connection and sqlens.connection. Nothing cross-checks the two, so set them together — a report labeled tenant_eu that read tenant_us is exactly the confusion this guard exists to prevent.

explicit is the answer in both directions. The mode is already none by default, and the signals are read on every run that is not explicit, so there is nothing to switch off: a project the heuristic reads wrongly declares explicit and names the database it wants the report to be about. That costs one line and buys a header that says what the findings apply to. Declaring explicit and naming nobody is its own refusal, with its own message — a project in that state already knows it has tenants.

There is deliberately no fan-out. No --all-tenants, no loop over tenant connections, no merged report. An aggregate over tenants is a different kind of claim — it would have to say which tenants disagreed and why — and a loop without that produces a report nobody can act on.

The privileges it needs

SQLens is built to run as an ordinary, least-privileged account, and neither engine needs a superuser. The exact grants, per engine, are on the catalog reader page; this section is about the other half — what happens when the role cannot see something.

Both engines withhold silently, in different ways, which is why the audit asks what this role may see before it reads rather than inferring it from an empty result afterwards:

• MySQL filters information_schema by privilege, without a word. The query that returns a database's tables to an administrator returns zero rows to an account without SELECT on them. No error, no warning — a clean, empty, complete-looking audit of a database the reader could not see. It is the most dangerous shape in this package's problem space, because the reading itself succeeded.
• PostgreSQL mostly answers, and hides the rest a row at a time. pg_class, pg_attribute and pg_index are world-readable, so the core of a reading works on a managed instance with no special grant at all. But pg_settings simply omits a row the role may not see, with no error either way.

Each area is probed on its own, and each one that comes back short is a named skip in the report rather than a missing paragraph in it:

What the role must reach	What is lost without it
PostgreSQL: pg_class, pg_attribute, pg_index	everything. An audit of this database would be empty rather than clean — which is why the skip is reported instead.
PostgreSQL: pg_constraint, pg_type	nothing can be concluded about foreign keys, checks, domains or enums.
PostgreSQL: pg_statistic (via pg_read_all_stats)	no check may reason about table size or row estimates.
PostgreSQL: pg_read_all_settings	settings that require elevated access are invisible — absent from pg_settings rather than refused.
MySQL: SELECT on each audited database	the database is invisible, and the reading comes back empty and complete-looking.
MySQL: performance_schema enabled and visible	nothing can be concluded from live statement instrumentation.

What degrades, and independently of what

A missing grant does not end a run. It narrows it, and the narrowing is reported — one named undetermined per gap, in a report that also says what it did read. These five degrade independently of each other, so a run can lose one and keep the rest:

1. The instance identity. One statement asks the server for its host, port, database, version and role. If it fails, the role becomes undetermined — which withholds every write-path rule, exactly as a replica does — and with no version to gate on, every rule with a version window is withheld too, under its own reason. On PostgreSQL over a Unix socket the host alone is undetermined (inet_server_addr() is null there, which is ordinary rather than a fault); the port, database, version and role still answer.
2. The catalog reading. Whatever could not be read travels out of the snapshot and into the result: an undetermined finding per skip, with its reason, plus a skipped line in the header. A reader scanning a clean summary can otherwise not tell a run that found nothing from one that could not look, and only the second needs a grant fixed.
3. The server settings. A reading that fails outright is said once, at run level, with the reason — otherwise every server-baseline rule would simply have no subject, which is byte-for-byte what a correctly configured server produces. A PostgreSQL role that is not a member of pg_read_all_settings gets a partial view, reported the same way: the variables that did come through are still judged, and a rule whose variable did not reports undetermined for itself.
4. Statistics and index usage. They are read beside the catalog, never folded into it, and a rule that needed a number it did not get reports that rather than assuming "small". A recommendation to lock a table, resting on a guess that it holds few rows, is the failure this prevents.
5. The topology. Behind a transaction pooler, consecutive statements can run on different backends, so a settings reading assembled from several of them describes no single machine. Every server-scoped check reports undetermined with the reason transaction_pooled, plus one run-level finding explaining the whole band. Schema findings are unaffected.

The reading also bounds itself in time (sqlens.catalog.budget_ms). Exceeding that is a named undetermined and never an abort: the objects already read are worth having, and killing the run would throw them away to report a problem the run itself caused.

None of these fails a run on its own — they are undetermined, not fail. That is the point of the third value, and it is also why a pipeline should not leave it at that: run the gate with --strict, or set sqlens.strict_undetermined, and a check that could not run leaves with exit code 3 instead of a green that proves less than it looks.

The ci profile is not enough on its own here

--profile=ci sets both strict switches — and it also sets level 4, which is where it stops being a drop-in for an audit gate. The audit rules start at level 5 on PostgreSQL and at level 2 on MySQL, so level 4 admits no PostgreSQL audit rule at all and one MySQL rule. On PostgreSQL the run therefore refuses rather than escalating anything:

php artisan sqlens:audit --connection=pgsql --profile=ci

  level<=4 active-rules=0
  [undetermined]  CAP.L0.NO_ACTIVE_RULES  sqlens.audit

Exit code 2, not 3 — and that is the guard working, not a bug: a scope that admits no rule is a misconfiguration, because "nothing was checked" must never leave as a pass. It is still not the gate you wanted. Name the level explicitly beside the profile:

php artisan sqlens:audit --connection=pgsql --profile=ci --level=6

The flag always beats the level a profile presets, so this keeps the profile's strictness and audits at a level that has rules. The level a profile presets is calibrated for the lint suite, which runs over migration files from level 0 up; the audit suite reads a live catalog and its cheapest questions sit higher.

See also

• The catalog reader — the complete list of what a reading queries, the privileges per engine, and what managed databases withhold.
• Read/write splits — what Laravel does with a host list, what SQLens does instead, and how a transaction pooler changes what a reading is worth.
• Understanding undetermined — the three-valued model, and why a check that could not run is never a pass.
• The strictness levels — what each level adds, and why a level is an appetite rather than a severity.
• The lint suite — the exit-code table both suites share.
• The rules — one page per rule id.