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#[must_use]
105pub fn created_table_names(sql: &str) -> Vec<String> {
106    let Ok(parsed) = pg_query::parse(sql) else {
107        return Vec::new();
108    };
109    parsed
110        .protobuf
111        .stmts
112        .iter()
113        .filter_map(|raw| match raw.stmt.as_ref()?.node.as_ref()? {
114            Node::CreateStmt(create) => collect_create_stmt(create).map(|t| t.name().to_owned()),
115            _ => None,
116        })
117        .collect()
118}
119
120pub fn lint_declarative_schema(sql: &str, source: &str) -> Result<(), Vec<LintError>> {
121    let parsed = match pg_query::parse(sql) {
122        Ok(p) => p,
123        Err(e) => {
124            return Err(vec![LintError {
125                line: 1,
126                column: 1,
127                severity: LintSeverity::Error,
128                message: format!("SQL parse failed: {e}"),
129                source: source.to_owned(),
130            }]);
131        },
132    };
133
134    let line_index = LineIndex::new(sql);
135    let stmts = &parsed.protobuf.stmts;
136    let (tables, mut errors) = classify_pass(stmts, sql, &line_index, source);
137    errors.extend(column_ref_pass(stmts, sql, &line_index, &tables, source));
138
139    if errors.iter().any(|e| e.severity == LintSeverity::Error) {
140        return Err(errors);
141    }
142    Ok(())
143}
144
145fn classify_pass(
146    stmts: &[pg_query::protobuf::RawStmt],
147    sql: &str,
148    line_index: &LineIndex,
149    source: &str,
150) -> (Vec<TableDef>, Vec<LintError>) {
151    let mut errors: Vec<LintError> = Vec::new();
152    let mut tables: Vec<TableDef> = Vec::new();
153
154    for raw in stmts {
155        let location = stmt_start_offset(sql, raw.stmt_location.max(0) as usize);
156        let (line, col) = line_index.position(location);
157        let loc = StmtLoc { line, col, source };
158
159        let Some(stmt) = raw.stmt.as_ref() else {
160            continue;
161        };
162        let Some(node) = stmt.node.as_ref() else {
163            continue;
164        };
165
166        match node {
167            Node::CreateStmt(create) => {
168                if let Some(table) = collect_create_stmt(create) {
169                    tables.push(table);
170                }
171                if let Some(warn) = warn_create_table_missing_if_not_exists(create, &loc) {
172                    errors.push(warn);
173                }
174            },
175            Node::IndexStmt(_)
176            | Node::CreateFunctionStmt(_)
177            | Node::ViewStmt(_)
178            | Node::CreateTrigStmt(_)
179            | Node::CompositeTypeStmt(_)
180            | Node::CreateEnumStmt(_)
181            | Node::CommentStmt(_) => {},
182            Node::CreateExtensionStmt(ext) => {
183                if !ext.if_not_exists {
184                    errors.push(LintError {
185                        line,
186                        column: col,
187                        severity: LintSeverity::Warning,
188                        message: "CREATE EXTENSION without IF NOT EXISTS".into(),
189                        source: source.to_owned(),
190                    });
191                }
192            },
193            other => {
194                if let Some(reason) = imperative_reason(other) {
195                    errors.push(LintError {
196                        line,
197                        column: col,
198                        severity: LintSeverity::Error,
199                        message: format!(
200                            "imperative SQL in declarative schema: {reason} — move to \
201                             schema/migrations/NNN_<name>.sql"
202                        ),
203                        source: source.to_owned(),
204                    });
205                }
206            },
207        }
208    }
209
210    (tables, errors)
211}
212
213fn column_ref_pass(
214    stmts: &[pg_query::protobuf::RawStmt],
215    sql: &str,
216    line_index: &LineIndex,
217    tables: &[TableDef],
218    source: &str,
219) -> Vec<LintError> {
220    let mut errors: Vec<LintError> = Vec::new();
221
222    for raw in stmts {
223        let Some(stmt) = raw.stmt.as_ref() else {
224            continue;
225        };
226        let Some(node) = stmt.node.as_ref() else {
227            continue;
228        };
229        let location = stmt_start_offset(sql, raw.stmt_location.max(0) as usize);
230        let (line, col) = line_index.position(location);
231        let loc = StmtLoc { line, col, source };
232
233        match node {
234            Node::IndexStmt(idx) => {
235                check_index_columns(idx, tables, &loc, &mut errors);
236            },
237            Node::ViewStmt(view) => {
238                check_view_columns(view, tables, &loc, &mut errors);
239            },
240            _ => {},
241        }
242    }
243
244    errors
245}
systemprompt_database/services/schema_linter/mod.rs

systemprompt_database/services/schema_linter/
mod.rs
