:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
//! Read-only access to the stats database, returning rows as JSON objects so the
//! REST and MCP layers can share every query without per-endpoint structs.
use anyhow::{Context, Result};
use chrono::{DateTime, Utc};
use deadpool_postgres::{Manager, ManagerConfig, Pool, RecyclingMethod};
use serde_json::{json, Map, Value};
use tokio_postgres::types::{ToSql, Type};
/// Serialised-response ceiling for raw SQL. Row count is bounded by LIMIT; this bounds
/// bytes so a wide or `repeat()`-style query can't exhaust the pod's memory.
pub const RAW_SQL_MAX_BYTES: usize = 8 * 1024 * 1024;
#[derive(Clone)]
pub struct Db {
pool: Pool,
}
impl Db {
pub async fn connect(url: &str) -> Result<Self> {
let mut cfg: tokio_postgres::Config = url.parse().context("parsing PGAPI_DATABASE_URL")?;
cfg.ssl_mode(tls_policy(&cfg));
let tls = tls_connector();
let mgr = Manager::from_config(
cfg,
tls,
ManagerConfig {
recycling_method: RecyclingMethod::Fast,
},
);
let pool = Pool::builder(mgr)
.max_size(16)
.post_create(deadpool_postgres::Hook::async_fn(|c, _| Box::pin(async move {
// The API is read-only by construction; make the session enforce it too. A
// connection that cannot be made read-only is rejected, never pooled.
c.batch_execute("SET default_transaction_read_only = on; SET statement_timeout = 30000; SET application_name = 'pgapi'")
.await
.map_err(|e| {
tracing::error!(error = %e, "could not apply read-only session settings; dropping connection");
deadpool_postgres::HookError::Backend(e)
})
})))
.build()?;
let db = Self { pool };
db.query("SELECT 1", &[])
.await
.context("stats database unreachable")?;
Ok(db)
}
pub async fn ping(&self) -> bool {
self.query("SELECT 1", &[]).await.is_ok()
}
pub async fn query(&self, sql: &str, params: &[&(dyn ToSql + Sync)]) -> Result<Vec<Value>> {
let c = self.pool.get().await?;
let rows = c
.query(sql, params)
.await
.with_context(|| format!("query failed: {}", sql.lines().next().unwrap_or("")))?;
rows.iter().map(row_to_json).collect()
}
/// Run one statement in a read-only transaction with a short timeout, then reset
/// the session with `DISCARD ALL` so nothing it did (advisory locks, temp objects,
/// SET) outlives the request. If the reset fails the connection is dropped.
pub async fn query_isolated(&self, sql: &str) -> Result<Value> {
let mut c = self.pool.get().await?;
let result = async {
let tx = c.build_transaction().read_only(true).start().await?;
tx.batch_execute("SET LOCAL statement_timeout = 15000; SET LOCAL lock_timeout = 1000")
.await?;
// Stream rows and stop once the serialised response would exceed the budget;
// dropping the stream and rolling back cancels the server side. Row count is
// capped by the caller's LIMIT, bytes are capped here.
use futures_util::TryStreamExt;
let params: [&(dyn ToSql + Sync); 0] = [];
let stream = tx
.query_raw(sql, params)
.await
.with_context(|| format!("query failed: {}", sql.lines().next().unwrap_or("")))?;
let mut stream = std::pin::pin!(stream);
let mut out = Vec::new();
let mut bytes = 0usize;
while let Some(row) = stream.try_next().await.context("query failed")? {
let v = row_to_json(&row)?;
bytes += serde_json::to_vec(&v).map(|b| b.len()).unwrap_or(0);
anyhow::ensure!(
bytes <= RAW_SQL_MAX_BYTES,
"result exceeds {} MiB; narrow the query or select fewer columns",
RAW_SQL_MAX_BYTES / (1024 * 1024)
);
out.push(v);
}
tx.rollback().await?;
Ok::<_, anyhow::Error>(out)
}
.await;
if c.batch_execute(
"DISCARD ALL; SET default_transaction_read_only = on; SET statement_timeout = 30000",
)
.await
.is_err()
{
// Don't hand a dirty session back to the pool.
drop(deadpool_postgres::Object::take(c));
}
Ok(json!(result?))
}
pub async fn query_one(&self, sql: &str, params: &[&(dyn ToSql + Sync)]) -> Result<Value> {
Ok(self
.query(sql, params)
.await?
.into_iter()
.next()
.unwrap_or(Value::Null))
}
}
pub fn row_to_json(r: &tokio_postgres::Row) -> Result<Value> {
let mut m = Map::new();
for (i, col) in r.columns().iter().enumerate() {
let v = match *col.type_() {
Type::BOOL => r.try_get::<_, Option<bool>>(i)?.map(Value::from),
Type::INT2 => r.try_get::<_, Option<i16>>(i)?.map(Value::from),
Type::INT4 => r.try_get::<_, Option<i32>>(i)?.map(Value::from),
// JavaScript numbers lose precision past 2^53; query/plan ids are full
// int64 hashes, so ship those as strings.
Type::INT8 => r.try_get::<_, Option<i64>>(i)?.map(|v| {
if v.unsigned_abs() > (1u64 << 53) {
Value::from(v.to_string())
} else {
Value::from(v)
}
}),
Type::OID => r.try_get::<_, Option<u32>>(i)?.map(Value::from),
Type::FLOAT4 => r.try_get::<_, Option<f32>>(i)?.map(|x| json!(x)),
Type::FLOAT8 => r.try_get::<_, Option<f64>>(i)?.map(|x| json!(x)),
Type::NUMERIC => r.try_get::<_, Option<rust_decimal::Decimal>>(i)?.map(|d| {
let f: f64 = d.try_into().unwrap_or(f64::NAN);
json!(f)
}),
Type::TIMESTAMPTZ => r.try_get::<_, Option<DateTime<Utc>>>(i)?.map(|t| json!(t)),
Type::JSON | Type::JSONB => r.try_get::<_, Option<Value>>(i)?,
// Functions returning void (raw SQL calling e.g. pg_advisory_lock) produce a
// void column; render it as null rather than failing the whole row.
Type::VOID => None,
Type::TEXT_ARRAY | Type::VARCHAR_ARRAY | Type::NAME_ARRAY => {
r.try_get::<_, Option<Vec<String>>>(i)?.map(|v| json!(v))
}
Type::INT8_ARRAY => r.try_get::<_, Option<Vec<i64>>>(i)?.map(|v| json!(v)),
Type::TEXT | Type::VARCHAR | Type::NAME | Type::BPCHAR | Type::CHAR | Type::UNKNOWN => {
r.try_get::<_, Option<String>>(i)?.map(Value::from)
}
_ => anyhow::bail!(
"unsupported column type {} for {}; cast in SQL",
col.type_(),
col.name()
),
};
m.insert(col.name().to_string(), v.unwrap_or(Value::Null));
}
Ok(Value::Object(m))
}
/// TLS is required unless the parsed URL carries `sslmode=disable` (local dev against
/// a plain Postgres). RDS always offers TLS, so deployed URLs carry no parameter.
fn tls_policy(cfg: &tokio_postgres::Config) -> tokio_postgres::config::SslMode {
use tokio_postgres::config::SslMode;
match cfg.get_ssl_mode() {
SslMode::Disable => SslMode::Disable,
_ => SslMode::Require,
}
}
fn tls_connector() -> tokio_postgres_rustls::MakeRustlsConnect {
let mut roots = rustls::RootCertStore::empty();
let native = rustls_native_certs::load_native_certs();
for err in &native.errors {
tracing::warn!(error = %err, "failed to load a native certificate");
}
for cert in native.certs {
if let Err(e) = roots.add(cert) {
tracing::warn!(error = %e, "failed to add a native certificate to root store");
}
}
let tls_cfg = rustls::ClientConfig::builder()
.with_root_certificates(roots)
.with_no_client_auth();
tokio_postgres_rustls::MakeRustlsConnect::new(tls_cfg)
}