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//! Column resolution does not descend into:
46//!
47//! - PL/pgSQL function bodies (resolved by Postgres at function call time)
48//! - `CHECK` constraint expressions (resolved by Postgres at table creation)
49//! - Trigger function bodies
50//!
51//! These are deferred so the linter behaves identically to the database for
52//! anything it cannot statically prove, avoiding false positives on
53//! late-bound names.
54//!
55//! Copyright (c) systemprompt.io — Business Source License 1.1.
56//! See <https://systemprompt.io> for licensing details.
57
58mod classify;
59mod columns;
60mod location;
61
62use std::fmt;
63
64use pg_query::protobuf::node::Node;
65
66use classify::{imperative_reason, warn_create_table_missing_if_not_exists};
67use columns::{TableDef, check_index_columns, check_view_columns, collect_create_stmt};
68use location::{LineIndex, StmtLoc, stmt_start_offset};
69
70#[derive(Debug, Clone, Copy, PartialEq, Eq)]
71pub enum LintSeverity {
72    Error,
73    Warning,
74}
75
76impl fmt::Display for LintSeverity {
77    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
78        match self {
79            Self::Error => f.write_str("error"),
80            Self::Warning => f.write_str("warning"),
81        }
82    }
83}
84
85#[derive(Debug, Clone, PartialEq, Eq)]
86pub struct LintError {
87    pub line: u32,
88    pub column: u32,
89    pub severity: LintSeverity,
90    pub message: String,
91    pub source: String,
92}
93
94impl fmt::Display for LintError {
95    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
96        write!(
97            f,
98            "{}:{}:{}: {}: {}",
99            self.source, self.line, self.column, self.severity, self.message
100        )
101    }
102}
103
104/// The single source of truth for which tables an extension *owns*.
105///
106/// Ownership is derived from the declarative schema, never hand-authored. A
107/// parse failure yields an empty list — the linter reports the parse error
108/// separately.
109#[must_use]
110pub fn created_table_names(sql: &str) -> Vec<String> {
111    let Ok(parsed) = pg_query::parse(sql) else {
112        return Vec::new();
113    };
114    parsed
115        .protobuf
116        .stmts
117        .iter()
118        .filter_map(|raw| match raw.stmt.as_ref()?.node.as_ref()? {
119            Node::CreateStmt(create) => collect_create_stmt(create).map(|t| t.name().to_owned()),
120            _ => None,
121        })
122        .collect()
123}
124
125/// `source` is not read; it is the label stamped into error messages (typically
126/// the schema table name or the file path).
127pub fn lint_declarative_schema(sql: &str, source: &str) -> Result<(), Vec<LintError>> {
128    let parsed = match pg_query::parse(sql) {
129        Ok(p) => p,
130        Err(e) => {
131            return Err(vec![LintError {
132                line: 1,
133                column: 1,
134                severity: LintSeverity::Error,
135                message: format!("SQL parse failed: {e}"),
136                source: source.to_owned(),
137            }]);
138        },
139    };
140
141    let line_index = LineIndex::new(sql);
142    let stmts = &parsed.protobuf.stmts;
143    let (tables, mut errors) = classify_pass(stmts, sql, &line_index, source);
144    errors.extend(column_ref_pass(stmts, sql, &line_index, &tables, source));
145
146    if errors.iter().any(|e| e.severity == LintSeverity::Error) {
147        return Err(errors);
148    }
149    Ok(())
150}
151
152fn classify_pass(
153    stmts: &[pg_query::protobuf::RawStmt],
154    sql: &str,
155    line_index: &LineIndex,
156    source: &str,
157) -> (Vec<TableDef>, Vec<LintError>) {
158    let mut errors: Vec<LintError> = Vec::new();
159    let mut tables: Vec<TableDef> = Vec::new();
160
161    for raw in stmts {
162        let location = stmt_start_offset(sql, raw.stmt_location.max(0) as usize);
163        let (line, col) = line_index.position(location);
164        let loc = StmtLoc { line, col, source };
165
166        let Some(stmt) = raw.stmt.as_ref() else {
167            continue;
168        };
169        let Some(node) = stmt.node.as_ref() else {
170            continue;
171        };
172
173        match node {
174            Node::CreateStmt(create) => {
175                if let Some(table) = collect_create_stmt(create) {
176                    tables.push(table);
177                }
178                if let Some(warn) = warn_create_table_missing_if_not_exists(create, &loc) {
179                    errors.push(warn);
180                }
181            },
182            Node::IndexStmt(_)
183            | Node::CreateFunctionStmt(_)
184            | Node::ViewStmt(_)
185            | Node::CreateTrigStmt(_)
186            | Node::CompositeTypeStmt(_)
187            | Node::CreateEnumStmt(_)
188            | Node::CommentStmt(_) => {},
189            Node::CreateExtensionStmt(ext) => {
190                if !ext.if_not_exists {
191                    errors.push(LintError {
192                        line,
193                        column: col,
194                        severity: LintSeverity::Warning,
195                        message: "CREATE EXTENSION without IF NOT EXISTS".into(),
196                        source: source.to_owned(),
197                    });
198                }
199            },
200            other => {
201                if let Some(reason) = imperative_reason(other) {
202                    errors.push(LintError {
203                        line,
204                        column: col,
205                        severity: LintSeverity::Error,
206                        message: format!(
207                            "imperative SQL in declarative schema: {reason} — move to \
208                             schema/migrations/NNN_<name>.sql"
209                        ),
210                        source: source.to_owned(),
211                    });
212                }
213            },
214        }
215    }
216
217    (tables, errors)
218}
219
220fn column_ref_pass(
221    stmts: &[pg_query::protobuf::RawStmt],
222    sql: &str,
223    line_index: &LineIndex,
224    tables: &[TableDef],
225    source: &str,
226) -> Vec<LintError> {
227    let mut errors: Vec<LintError> = Vec::new();
228
229    for raw in stmts {
230        let Some(stmt) = raw.stmt.as_ref() else {
231            continue;
232        };
233        let Some(node) = stmt.node.as_ref() else {
234            continue;
235        };
236        let location = stmt_start_offset(sql, raw.stmt_location.max(0) as usize);
237        let (line, col) = line_index.position(location);
238        let loc = StmtLoc { line, col, source };
239
240        match node {
241            Node::IndexStmt(idx) => {
242                check_index_columns(idx, tables, &loc, &mut errors);
243            },
244            Node::ViewStmt(view) => {
245                check_view_columns(view, tables, &loc, &mut errors);
246            },
247            _ => {},
248        }
249    }
250
251    errors
252}
systemprompt_database/services/schema_linter/mod.rs

systemprompt_database/services/schema_linter/
mod.rs
