Skip to main content

everruns_durable/persistence/postgres/
schema.rs

1//! Standalone PostgreSQL schema for [`PostgresWorkflowEventStore`].
2//!
3//! The crate ships an [idempotent PostgreSQL schema](https://github.com/everruns/everruns/blob/main/crates/durable/schema/postgres.sql)
4//! so applications can create durable tables without the Everruns server's
5//! migrations. [`PostgresWorkflowEventStore::SCHEMA_SQL`] exposes the same SQL
6//! for custom migration tools. Running it against a server-migrated database
7//! is a no-op.
8
9use sqlx::PgPool;
10
11use super::{PostgresWorkflowEventStore, begin_transaction};
12
13/// Arbitrary, stable key for the transaction-scoped advisory lock that
14/// serializes concurrent [`PostgresWorkflowEventStore::migrate`] calls.
15const SCHEMA_LOCK_KEY: i64 = 0x6576_6572_6475_7261; // "everdura"
16
17/// The table whose comment records which [`SCHEMA_SQL`] a database last took.
18///
19/// Decision: re-applying the schema is not free. `CREATE INDEX IF NOT EXISTS`
20/// and `CREATE OR REPLACE TRIGGER` lock their tables before they find nothing
21/// to do, so a process migrating while another runs tasks could deadlock
22/// with it. A start-up whose schema is already applied therefore only reads
23/// this comment (no lock beyond a catalog read) and skips the DDL. A comment
24/// keeps the schema itself identical to the server's migrations.
25///
26/// [`SCHEMA_SQL`]: PostgresWorkflowEventStore::SCHEMA_SQL
27const SCHEMA_MARKER_TABLE: &str = "durable_workflow_instances";
28
29/// `durable-schema:<FNV-1a of SCHEMA_SQL>`: stable across builds and Rust
30/// versions, unlike `std`'s hasher.
31fn schema_marker() -> String {
32    let mut hash: u64 = 0xcbf2_9ce4_8422_2325;
33    for byte in PostgresWorkflowEventStore::SCHEMA_SQL.bytes() {
34        hash ^= u64::from(byte);
35        hash = hash.wrapping_mul(0x0000_0100_0000_01b3);
36    }
37    format!("durable-schema:{hash:016x}")
38}
39
40/// The schema marker the database holds, if any.
41async fn applied_marker<'e, E>(executor: E) -> Result<Option<String>, sqlx::Error>
42where
43    E: sqlx::PgExecutor<'e>,
44{
45    sqlx::query_scalar("SELECT obj_description(to_regclass($1), 'pg_class')")
46        .bind(SCHEMA_MARKER_TABLE)
47        .fetch_one(executor)
48        .await
49}
50
51impl PostgresWorkflowEventStore {
52    /// The idempotent SQL that [`migrate`](Self::migrate) applies.
53    ///
54    /// Exposed for applications that manage schema with their own migration
55    /// tool: copy it into a migration instead of calling `migrate` at start-up.
56    pub const SCHEMA_SQL: &'static str = include_str!("../../../schema/postgres.sql");
57
58    /// Create or update the durable tables, indexes, functions and triggers.
59    ///
60    /// Safe to call on every start-up: every statement is idempotent, the whole
61    /// schema is applied in one transaction, concurrent callers serialize on
62    /// an advisory lock, and a database that already took this exact schema
63    /// is only read, not locked. Apply a new schema version before traffic
64    /// that uses it, as with any migration. A database already migrated by the Everruns server
65    /// is left unchanged. Objects land in the first schema of the connection's
66    /// `search_path`, normally `public`.
67    ///
68    /// Requires PostgreSQL 14 or newer and no extensions.
69    ///
70    /// # Example
71    ///
72    /// ```no_run
73    /// use everruns_durable::PostgresWorkflowEventStore;
74    ///
75    /// # async fn run() -> Result<(), sqlx::Error> {
76    /// let pool = sqlx::PgPool::connect("postgres://localhost/my_app").await?;
77    /// PostgresWorkflowEventStore::migrate(&pool).await?;
78    /// let store = PostgresWorkflowEventStore::new(pool);
79    /// # let _ = store;
80    /// # Ok(()) }
81    /// ```
82    pub async fn migrate(pool: &PgPool) -> Result<(), sqlx::Error> {
83        let marker = schema_marker();
84        if applied_marker(pool).await?.as_deref() == Some(marker.as_str()) {
85            return Ok(());
86        }
87        let mut tx = begin_transaction(pool).await?;
88        // `CREATE ... IF NOT EXISTS` reports every existing object as a
89        // NOTICE; keep re-runs quiet.
90        sqlx::query("SET LOCAL client_min_messages = warning")
91            .execute(&mut *tx)
92            .await?;
93        sqlx::query("SELECT pg_advisory_xact_lock($1)")
94            .bind(SCHEMA_LOCK_KEY)
95            .execute(&mut *tx)
96            .await?;
97        // Another caller may have applied it while this one waited.
98        if applied_marker(&mut *tx).await?.as_deref() == Some(marker.as_str()) {
99            return crate::commit_transaction(tx).await;
100        }
101        sqlx::raw_sql(Self::SCHEMA_SQL).execute(&mut *tx).await?;
102        // COMMENT takes no bind parameters; the statement is built only from
103        // a constant table name and a hex hash, so nothing untrusted reaches it.
104        let comment = format!("COMMENT ON TABLE {SCHEMA_MARKER_TABLE} IS '{marker}'");
105        sqlx::raw_sql(sqlx::AssertSqlSafe(comment))
106            .execute(&mut *tx)
107            .await?;
108        crate::commit_transaction(tx).await
109    }
110
111    /// Connect to the database at `url`, apply the schema with
112    /// [`migrate`](Self::migrate), and return a store over the new pool.
113    ///
114    /// The one-call start for an application that keeps no pool of its own,
115    /// so it never touches the database driver. An application that manages
116    /// its pool or its schema itself builds the store with
117    /// [`new`](Self::new) instead.
118    ///
119    /// # Example
120    ///
121    /// ```no_run
122    /// use everruns_durable::PostgresWorkflowEventStore;
123    ///
124    /// # async fn run() -> Result<(), sqlx::Error> {
125    /// let store = PostgresWorkflowEventStore::connect("postgres://localhost/my_app").await?;
126    /// # let _ = store;
127    /// # Ok(()) }
128    /// ```
129    pub async fn connect(url: &str) -> Result<Self, sqlx::Error> {
130        let pool = PgPool::connect(url).await?;
131        Self::migrate(&pool).await?;
132        Ok(Self::new(pool))
133    }
134}