systemprompt_database/services/schema_linter/
mod.rs

1//! Declarative-schema linter.
2//!
3//! Parses each schema with [`pg_query`] (the actual `PostgreSQL` parser,
4//! exposed as a protobuf AST) and walks top-level statements. Classification is
5//! by AST node variant rather than keyword tokens, so identifier-equal strings
6//! such as a column literally named `alter` do not produce false positives,
7//! and dollar-quoted PL/pgSQL bodies are skipped at the parser level.
8//!
9//! ## Allowed top-level statements
10//!
11//! - `CreateStmt` — `CREATE TABLE`
12//! - `IndexStmt` — `CREATE [UNIQUE] INDEX`
13//! - `CreateFunctionStmt`
14//! - `ViewStmt` — `CREATE [OR REPLACE] VIEW`
15//! - `CreateTrigStmt`
16//! - `CompositeTypeStmt` — `CREATE TYPE … AS (…)`
17//! - `CreateEnumStmt` — `CREATE TYPE … AS ENUM`
18//! - `CreateExtensionStmt`
19//! - `CommentStmt` — `COMMENT ON …`
20//! - `DropStmt` — only `DROP VIEW`/`MATERIALIZED VIEW`/`INDEX`/`TRIGGER … IF
21//!   EXISTS`. These objects are stateless derived artifacts: dropping one loses
22//!   no data and the sibling `CREATE …` statement rebuilds it, so the pair
23//!   stays idempotent. `DROP TABLE`/`DROP COLUMN` remain rejected.
24//!
25//! ## Rejected top-level statements
26//!
27//! - `AlterTableStmt`
28//! - `DropStmt` — except the stateless-object carve-out above
29//! - `InsertStmt` / `UpdateStmt` / `DeleteStmt` / `TruncateStmt`
30//! - `GrantStmt` / `RevokeStmt`
31//! - `RenameStmt` — any object rename
32//! - `DoStmt` — anonymous `DO $$ … $$` blocks
33//! - Any bare `SELECT`/`COPY`/imperative statement
34//!
35//! ## Semantic checks
36//!
37//! For statements that reference columns of a table defined elsewhere in the
38//! same input (`CREATE INDEX`, `CREATE VIEW`), the linter resolves the
39//! `(table, column)` pair against an in-input schema graph built from sibling
40//! `CREATE TABLE` nodes. References to tables that are not declared in the
41//! same input (e.g. cross-extension `REFERENCES`) are intentionally not
42//! resolved — the parser sees those as forward references the database itself
43//! validates at apply-time.
44//!
45//! A `FOREIGN KEY` whose referenced table is declared in the same input must
46//! find a `PRIMARY KEY` or `UNIQUE` on exactly the referenced columns in that
47//! table's `CREATE TABLE`. The installer applies foreign keys last, after
48//! migrations and indexes, so the key itself installs anywhere — but on a
49//! fresh database only the declarative schema runs, and the uniqueness has to
50//! be declared where the key can see it. For this rule the "input" is every
51//! schema file of one extension together ([`lint_declarative_schemas`]);
52//! positions are still reported per file.
53//!
54//! Column resolution does not descend into:
55//!
56//! - PL/pgSQL function bodies (resolved by Postgres at function call time)
57//! - `CHECK` constraint expressions (resolved by Postgres at table creation)
58//! - Trigger function bodies
59//!
60//! These are deferred so the linter behaves identically to the database for
61//! anything it cannot statically prove, avoiding false positives on
62//! late-bound names.
63//!
64//! The per-statement rules and column references are checked per input with
65//! that input's own line numbers; table definitions accumulate across inputs
66//! so a foreign key in one file resolves the table another file declares.
67//!
68//! Both lint entry points return `Ok(warnings)` when no error was found and
69//! `Err(findings)` — every warning and error — otherwise, so a caller never
70//! has to drop the advisory findings to learn the verdict. Table names from
71//! [`created_table_names`] are schema-qualified (`kb.docs`) when the
72//! `CREATE TABLE` names a schema and bare otherwise.
73//!
74//! Copyright (c) systemprompt.io — Business Source License 1.1.
75//! See <https://systemprompt.io> for licensing details.
76
77mod classify;
78mod columns;
79mod foreign_keys;
80mod location;
81mod passes;
82
83use std::fmt;
84
85use pg_query::protobuf::node::Node;
86
87use columns::{TableDef, collect_create_stmt};
88use location::LineIndex;
89use passes::{classify_pass, column_ref_pass, foreign_key_pass};
90
91#[derive(Debug, Clone, Copy, PartialEq, Eq)]
92pub enum LintSeverity {
93    Error,
94    Warning,
95}
96
97impl fmt::Display for LintSeverity {
98    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
99        match self {
100            Self::Error => f.write_str("error"),
101            Self::Warning => f.write_str("warning"),
102        }
103    }
104}
105
106#[derive(Debug, Clone, PartialEq, Eq)]
107pub struct LintError {
108    pub line: u32,
109    pub column: u32,
110    pub severity: LintSeverity,
111    pub message: String,
112    pub source: String,
113}
114
115impl fmt::Display for LintError {
116    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
117        write!(
118            f,
119            "{}:{}:{}: {}: {}",
120            self.source, self.line, self.column, self.severity, self.message
121        )
122    }
123}
124
125pub fn created_table_names(sql: &str) -> Result<Vec<String>, pg_query::Error> {
126    let parsed = pg_query::parse(sql)?;
127    Ok(parsed
128        .protobuf
129        .stmts
130        .iter()
131        .filter_map(|raw| match raw.stmt.as_ref()?.node.as_ref()? {
132            Node::CreateStmt(create) => collect_create_stmt(create).map(|t| t.qualified_name()),
133            _ => None,
134        })
135        .collect())
136}
137
138pub fn lint_declarative_schema(sql: &str, source: &str) -> Result<Vec<LintError>, Vec<LintError>> {
139    lint_declarative_schemas(&[(source, sql)])
140}
141
142pub fn lint_declarative_schemas(inputs: &[(&str, &str)]) -> Result<Vec<LintError>, Vec<LintError>> {
143    let mut errors: Vec<LintError> = Vec::new();
144    let mut parsed_inputs = Vec::with_capacity(inputs.len());
145    let mut tables: Vec<TableDef> = Vec::new();
146
147    for (source, sql) in inputs {
148        let parsed = match pg_query::parse(sql) {
149            Ok(p) => p,
150            Err(e) => {
151                errors.push(LintError {
152                    line: 1,
153                    column: 1,
154                    severity: LintSeverity::Error,
155                    message: format!("SQL parse failed: {e}"),
156                    source: (*source).to_owned(),
157                });
158                continue;
159            },
160        };
161        let line_index = LineIndex::new(sql);
162        let (found, mut found_errors) =
163            classify_pass(&parsed.protobuf.stmts, sql, &line_index, source);
164        errors.append(&mut found_errors);
165        errors.extend(column_ref_pass(
166            &parsed.protobuf.stmts,
167            sql,
168            &line_index,
169            &found,
170            source,
171        ));
172        tables.extend(found);
173        parsed_inputs.push((*source, *sql, parsed, line_index));
174    }
175
176    for (source, sql, parsed, line_index) in &parsed_inputs {
177        errors.extend(foreign_key_pass(
178            &parsed.protobuf.stmts,
179            sql,
180            line_index,
181            &tables,
182            source,
183        ));
184    }
185
186    if errors.iter().any(|e| e.severity == LintSeverity::Error) {
187        return Err(errors);
188    }
189    Ok(errors)
190}
systemprompt_database/services/schema_linter/mod.rs

systemprompt_database/services/schema_linter/
mod.rs
