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}