"""The event store on PostgreSQL: jsonb, and a GIN index instead of a tag table.
`MeshStore` (store.py) holds every rule that decides set membership, and a
backend supplies six primitives: _put, _drop, _all, _get, _tombstone,
_tombstoned_by. Narrowing is optional - the base can read everything and
filter in Python and still be correct - so what is here beyond the six is
index work, and the shared vectors prove it gives the same answers as the
backend that scans.
Not a port of SqliteStore. That one keeps a `tags` table because SQLite has
no indexable way into a JSON array, and pays for it with an EXISTS subquery
per tag name, an `_index_tags` write per event and a `_backfill_tags` sweep on
every open. A jsonb containment test subsumes all three:
json -> 'tags' @> '[["t","bristol"]]'
is one indexable predicate, with NIP-01's own semantics falling out of the
shape - values within a name OR together, different names AND. So there is no
tag table, no secondary write per event, and no backfill.
The fingerprint is safe over jsonb. `fingerprint_range` is taken over event
IDS (`enumerate_range`), never over stored bytes, and an id is a hex string
and `created_at` an integer - both round-trip exactly. Key order does not
matter either: `crypto.canonical` sorts keys itself, so a dict read back from
jsonb re-canonicalises to the same bytes it was written from.
The driver is optional and imported lazily. `fleetmesh_core` depends on
nothing, and nothing here changes that for a node on SQLite.
"""
from __future__ import annotations
import contextlib
import json
from typing import Any, Iterator, Mapping, Optional, Sequence
from ..crypto import crypto
from ..wire.interfaces import EventLike
from .store import MeshStore, compiled, expiration, matches
ERROR_HINT = ('postgres backend unavailable: pip install "psycopg[binary]" or -r requirements-postgres.txt '
'(optional; SQLite is the default)')
[docs]def driver() -> Any:
"""psycopg, or a refusal naming the remedy rather than an ImportError."""
try:
import psycopg # noqa: PLC0415
except ImportError as e:
raise RuntimeError(ERROR_HINT) from e
return psycopg
[docs]def available() -> bool:
try:
driver()
return True
except RuntimeError:
return False
SCHEMA = (
'CREATE TABLE IF NOT EXISTS events ('
' id TEXT PRIMARY KEY, pubkey TEXT, created_at BIGINT, kind INTEGER, json JSONB NOT NULL)',
'CREATE INDEX IF NOT EXISTS events_created_at ON events (created_at, id)',
# Supersession asks for one author's events of one kind on every
# replaceable insert, and a deletion asks for one author's events.
'CREATE INDEX IF NOT EXISTS events_author_kind ON events (pubkey, kind)',
# NIP-01 indexes single-letter tags and only those. `jsonb_path_ops` is the
# smaller, faster operator class and supports exactly the containment test
# below, which is all that is asked of it.
"CREATE INDEX IF NOT EXISTS events_tags ON events USING GIN ((json -> 'tags') jsonb_path_ops)",
'CREATE TABLE IF NOT EXISTS tombstones ('
' key TEXT, deleter TEXT, PRIMARY KEY (key, deleter))',
)
[docs]class PostgresStore(MeshStore):
"""The event store on PostgreSQL. One connection, as the node has one writer."""
[docs] def __init__(self, url: str, schema: str = 'public') -> None:
"""`schema` namespaces the two tables inside the node's database.
The default puts them where an unqualified name already looks, which
is what a node with nothing else in its database wants. Naming one
keeps the event tier's two tables clear of the state tier's, which is
the arrangement when a node gives both tiers one database.
"""
pg = driver()
if not schema.replace('_', '').isalnum():
raise ValueError('schema must be alphanumeric: %r' % schema)
self.url: str = url
self.schema: str = schema
self.db: Any = pg.connect(url, autocommit=True)
with self.db.cursor() as cur:
if schema != 'public':
cur.execute('CREATE SCHEMA IF NOT EXISTS %s' % schema)
# Set on the session rather than written into each statement, so
# the SQL below stays the SQL a reader would expect.
cur.execute('SET search_path TO %s' % schema)
for ddl in SCHEMA:
cur.execute(ddl)
[docs] def close(self) -> None:
try:
self.db.close()
except Exception:
pass
@contextlib.contextmanager
def _atomic(self) -> Iterator[None]:
"""One transaction for one insertion (audit 2026-10-15, B4).
The connection is in autocommit, so every statement outside this is
its own transaction, as it always was. psycopg's transaction() issues
BEGIN even in autocommit, commits when the block returns and rolls
back when it raises; nested, it is a savepoint.
"""
with self.db.transaction():
yield
# ---- the six primitives ----------------------------------------------
def _put(self, event: EventLike) -> None:
# The upsert mirrors SqliteStore's INSERT OR REPLACE rather than
# choosing something. No path in the base reaches here with an id
# already stored - insert_event answers 'duplicate' first, and
# supersession drops before it puts - so the conflict arm is
# defensive, and it is the house dialect's arm.
#
# canonical() fixes the form going in, as it does in SqliteStore. It
# does not fix the bytes at rest: jsonb reparses and normalises them.
# That costs nothing, because fingerprint_range is taken over event
# ids and never over stored bytes, and canonical() sorts keys, so a
# dict read back out of jsonb canonicalises to what was written.
with self.db.cursor() as cur:
cur.execute(
'INSERT INTO events (id, pubkey, created_at, kind, json) '
'VALUES (%s,%s,%s,%s,%s) ON CONFLICT (id) DO UPDATE SET '
'pubkey = EXCLUDED.pubkey, created_at = EXCLUDED.created_at, '
'kind = EXCLUDED.kind, json = EXCLUDED.json',
(event['id'], event['pubkey'], int(event['created_at']), int(event['kind']),
crypto.canonical(event).decode()))
def _drop(self, event_id: str) -> None:
with self.db.cursor() as cur:
cur.execute('DELETE FROM events WHERE id = %s', (event_id,))
def _all(self) -> list[Mapping[str, Any]]:
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events')
return [self._row(r[0]) for r in cur.fetchall()]
def _get(self, event_id: str) -> Optional[Mapping[str, Any]]:
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events WHERE id = %s', (event_id,))
r = cur.fetchone()
return self._row(r[0]) if r else None
def _tombstone(self, key: str, deleter: str) -> None:
with self.db.cursor() as cur:
cur.execute('INSERT INTO tombstones VALUES (%s,%s) ON CONFLICT DO NOTHING',
(key, deleter))
def _tombstoned_by(self, key: str) -> set[str]:
with self.db.cursor() as cur:
cur.execute('SELECT deleter FROM tombstones WHERE key = %s', (key,))
return {r[0] for r in cur.fetchall()}
@staticmethod
def _row(value: Any) -> Mapping[str, Any]:
"""psycopg hands jsonb back already decoded; a text column would not."""
return value if isinstance(value, dict) else json.loads(value)
# ---- narrowing, which is index work and not membership ----------------
def _where(self, lo: int, hi: int, kinds: Optional[Sequence[int]] = None,
authors: Optional[Sequence[str]] = None,
tags: Optional[Mapping[str, Sequence[str]]] = None,
ids: Optional[Sequence[str]] = None
) -> Optional[tuple[str, list[Any]]]:
"""The SQL for a narrowing, or None when no row can match.
None rather than an always-false clause because an empty `kinds` is a
filter that admits nothing, and saying so here saves the round trip.
"""
where: list[str] = ['created_at BETWEEN %s AND %s']
args: list[Any] = [int(lo), int(hi)]
if kinds is not None:
ks = [int(k) for k in kinds]
if not ks:
return None
where.append('kind = ANY(%s)')
args.append(ks)
if authors is not None:
aus = list(authors)
if not aus:
return None
where.append('pubkey = ANY(%s)')
args.append(aus)
# By primary key, as SqliteStore does (audit 2026-10-15, E1). A
# non-string id can never match, and a mixed list would not bind.
if ids is not None:
wanted = [i for i in ids if isinstance(i, str)]
if not wanted:
return None
where.append('id = ANY(%s)')
args.append(wanted)
# One containment test per value, OR'd within a name and AND'd across
# names - NIP-01's semantics, and the GIN index answers each one.
for name, values in (tags or {}).items():
vs = list(values)
if not vs:
return None
ors = []
for v in vs:
ors.append("json -> 'tags' @> %s::jsonb")
args.append(json.dumps([[name, v]]))
where.append('(%s)' % ' OR '.join(ors))
return ' AND '.join(where), args
def _narrow(self, lo: int, hi: int, kinds: Optional[Sequence[int]] = None,
authors: Optional[Sequence[str]] = None,
tags: Optional[Mapping[str, Sequence[str]]] = None,
ids: Optional[Sequence[str]] = None) -> list[Mapping[str, Any]]:
clause = self._where(lo, hi, kinds, authors, tags, ids)
if clause is None:
return []
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events WHERE ' + clause[0], clause[1])
return [self._row(r[0]) for r in cur.fetchall()]
def _by_author_kind(self, pubkey: str, kind: int) -> list[Mapping[str, Any]]:
# Exactly one kind, unlike _narrow, which may return a superset.
# `_deleted_by_coordinate` asks for this author's kind 5s and reads an
# `a` tag off each answer, so an answer carrying some other kind that
# happens to name the coordinate would read as a deletion.
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events WHERE pubkey = %s AND kind = %s',
(pubkey, int(kind)))
return [self._row(r[0]) for r in cur.fetchall()]
def _by_author(self, pubkey: str) -> list[Mapping[str, Any]]:
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events WHERE pubkey = %s', (pubkey,))
return [self._row(r[0]) for r in cur.fetchall()]
[docs] def newest_matching(self, filt: Mapping[str, Any], since: int, until: int,
now: int, cap: int) -> list[Mapping[str, Any]]:
"""The bounded answer, ordered by the index and stopped at `cap`.
The inherited version holds every match as a parsed event so it can
sort - the whole store, on an open face. Here the database orders on
the index `events_created_at` already provides and rows are parsed one
at a time until enough have matched.
"""
if cap <= 0:
return []
lo, hi = since, until
if 'since' in filt:
lo = max(lo, filt['since'])
if 'until' in filt:
hi = min(hi, filt['until'])
want_tags = {k[1:]: v for k, v in filt.items()
if len(k) == 2 and k[0] == '#' and isinstance(v, (list, tuple))}
clause = self._where(lo, hi, filt.get('kinds'), filt.get('authors'), want_tags, filt.get('ids'))
if clause is None:
return []
rest = compiled(filt)
out: list[Mapping[str, Any]] = []
with self.db.cursor() as cur:
cur.execute('SELECT json FROM events WHERE ' + clause[0]
+ ' ORDER BY created_at DESC, id ASC', clause[1])
for row in cur:
ev = self._row(row[0])
exp = expiration(ev)
if exp is not None and exp <= now:
continue
if not matches(ev, rest):
continue
out.append(ev)
if len(out) >= cap:
break
return out