"""p2panda SQLite persistent operation log store.
Standard library only. Manages persistence and chain verification for:
- Append-only cryptographic operation logs.
- Monotonic sequence numbers and backlink verification.
- Author log indexing and query pagination.
"""
from __future__ import annotations
import json
import os
from typing import Any, Optional, Sequence
from fleetmesh_core.conformance.errors import FleetmeshError
from fleetmesh_core.wire.p2panda import P2PandaHeader, P2PandaOperation
from fleetmesh_node.compartment.schema import Column, Index, Table
from fleetmesh_node.storage import SqliteStore, store_errors
__all__ = [
'P2PandaStoreError',
'P2PandaLogStore',
]
[docs]class P2PandaStoreError(FleetmeshError):
"""Base error for p2panda storage operations."""
pass
from fleetmesh_node.protocols.isotime import now_iso as _now_iso # noqa: E402
_COLS = ('op_id, author, seq_num, backlink, previous_seq_num, payload_size, payload_hash, '
'timestamp, signature, version, body, extensions_json, created_at')
"""The column order `_row_to_op` reads positionally.
The selects said `SELECT *` and the rows were indexed by name through a
`sqlite3.Row` factory. The base hands back plain tuples, so the order is
written down once here and every select names it - which is sturdier anyway:
`SELECT *` plus positional indexing would break on the next column added.
"""
[docs]class P2PandaLogStore(SqliteStore):
"""Append-only p2panda operation logs, one row per operation.
A `SqliteStore` rather than a hand-rolled connection: the base owns the
connection, the lock, the per-store schema version, transaction control and
the dialect that rewrites placeholders and upserts for another backend.
This store opened its own connection per call and declared its schema as a
SQL string, so it was SQLite-only and invisible to the locator - see
docs/storage-backends.md.
"""
SCHEMA = (
Table('p2panda_operations', (
Column('op_id', 'TEXT', primary_key=True),
Column('author', 'TEXT', not_null=True),
Column('seq_num', 'INTEGER', not_null=True),
Column('backlink', 'TEXT'),
Column('previous_seq_num', 'INTEGER'),
Column('payload_size', 'INTEGER', not_null=True),
Column('payload_hash', 'TEXT', not_null=True),
Column('timestamp', 'INTEGER', not_null=True),
Column('signature', 'TEXT', not_null=True),
Column('version', 'INTEGER', not_null=True, default='1'),
Column('body', 'BLOB'),
Column('extensions_json', 'TEXT'),
Column('created_at', 'TEXT', not_null=True),
), indices=(
# The log's real constraint: one operation per (author, seq_num).
# It was a table-level UNIQUE in the SQL; a unique index is the
# typed layer's way of saying it, and it also serves the lookup
# the old non-unique index on the same columns existed for.
Index('idx_p2p_ops_author_seq', ('author', 'seq_num'), unique=True),
Index('idx_p2p_ops_backlink', ('backlink',)),
)),
)
[docs] def __init__(self, db_path: str = ":memory:"):
if db_path != ":memory:":
os.makedirs(os.path.dirname(os.path.abspath(db_path)), exist_ok=True)
super().__init__(db_path)
def _row_to_op(self, row: Sequence[Any]) -> P2PandaOperation:
ext = None
if row[11]:
try:
ext = json.loads(row[11])
except Exception:
ext = None
header = P2PandaHeader(
version=int(row[9]),
public_key=str(row[1]),
signature=str(row[8]) if row[8] else None,
payload_size=int(row[5]),
payload_hash=str(row[6]),
timestamp=int(row[7]),
seq_num=int(row[2]),
backlink=str(row[3]) if row[3] else None,
previous_seq_num=int(row[4]) if row[4] is not None else None,
extensions=ext,
)
body = bytes(row[10]) if row[10] is not None else None
return P2PandaOperation(header=header, body=body)
[docs] def append_operation(self, op: P2PandaOperation, verify_chain: bool = True) -> bool:
"""Append an operation to the author's log.
Validates cryptographic integrity and chain monotonicity if verify_chain is True.
Returns True if newly inserted, False if idempotent duplicate.
"""
if verify_chain:
if not op.verify_signature():
raise P2PandaStoreError(f"Signature verification failed for operation {op.id}")
if not op.verify_payload():
raise P2PandaStoreError(f"Payload integrity verification failed for operation {op.id}")
author = op.author.lower()
op_id = op.id.lower()
with self._lock:
try:
with self.transaction() as cur:
# Check if identical op_id already exists
cur.execute("SELECT op_id FROM p2panda_operations WHERE op_id = ?", (op_id,))
if cur.fetchone() is not None:
return False
if verify_chain:
# Query latest operation for author
cur.execute(
"SELECT op_id, seq_num, timestamp FROM p2panda_operations WHERE author = ? ORDER BY seq_num DESC LIMIT 1",
(author,),
)
latest_row = cur.fetchone()
if latest_row is None:
# Genesis operation for author
if op.seq_num != 0:
raise P2PandaStoreError(
f"Genesis operation for author {author} must have seq_num 0, got {op.seq_num}"
)
if op.backlink is not None and op.backlink != '':
raise P2PandaStoreError(
f"Genesis operation for author {author} must have null backlink, got {op.backlink}"
)
else:
latest_op_id = str(latest_row[0]).lower()
latest_seq = int(latest_row[1])
latest_ts = int(latest_row[2])
expected_seq = latest_seq + 1
if op.seq_num != expected_seq:
if op.seq_num <= latest_seq:
raise P2PandaStoreError(
f"Fork or duplicate sequence conflict at seq_num={op.seq_num} for author {author}"
)
raise P2PandaStoreError(
f"Missing predecessor operation: author {author} is at seq_num {latest_seq}, got {op.seq_num}"
)
if op.backlink is None or op.backlink.lower() != latest_op_id:
raise P2PandaStoreError(
f"Backlink mismatch: expected {latest_op_id}, got {op.backlink}"
)
if op.header.timestamp < latest_ts:
raise P2PandaStoreError(
f"Operation timestamp ({op.header.timestamp}) is earlier than predecessor ({latest_ts})"
)
ext_json = json.dumps(op.header.extensions) if op.header.extensions is not None else None
cur.execute(
"""
INSERT INTO p2panda_operations (
op_id, author, seq_num, backlink, previous_seq_num,
payload_size, payload_hash, timestamp, signature,
version, body, extensions_json, created_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
(
op_id,
author,
op.seq_num,
op.backlink.lower() if op.backlink else None,
op.header.previous_seq_num,
op.header.payload_size,
op.header.payload_hash.lower(),
op.header.timestamp,
op.header.signature.lower() if op.header.signature else '',
op.header.version,
op.body,
ext_json,
_now_iso(),
),
)
return True
except store_errors() as e:
# A sequence collision or the author-seq uniqueness violation.
# `store_errors()` rather than `sqlite3.IntegrityError`: the
# narrow catch stays narrow and stays correct for a second
# driver with its own exception hierarchy.
raise P2PandaStoreError(f"Database integrity conflict: {e}") from e
[docs] def get_operation(self, op_id: str) -> Optional[P2PandaOperation]:
"""Fetch an operation by its op_id."""
clean_id = op_id.lower().strip()
with self._lock:
row = self._one("SELECT " + _COLS + " FROM p2panda_operations WHERE op_id = ?", (clean_id,))
return self._row_to_op(row) if row else None
[docs] def get_operation_by_seq(self, author: str, seq_num: int) -> Optional[P2PandaOperation]:
"""Fetch an operation by author public key and sequence number."""
clean_author = author.lower().strip()
with self._lock:
row = self._one(
"SELECT " + _COLS + " FROM p2panda_operations WHERE author = ? AND seq_num = ?",
(clean_author, int(seq_num)),
)
return self._row_to_op(row) if row else None
[docs] def get_latest_operation(self, author: str) -> Optional[P2PandaOperation]:
"""Fetch the latest operation in an author's log."""
clean_author = author.lower().strip()
with self._lock:
row = self._one(
"SELECT " + _COLS + " FROM p2panda_operations "
"WHERE author = ? ORDER BY seq_num DESC LIMIT 1",
(clean_author,),
)
return self._row_to_op(row) if row else None
[docs] def get_latest_seq(self, author: str) -> int:
"""Return the highest seq_num for author, or -1 if no operations exist."""
clean_author = author.lower().strip()
with self._lock:
row = self._one(
"SELECT MAX(seq_num) FROM p2panda_operations WHERE author = ?",
(clean_author,),
)
if row and row[0] is not None:
return int(row[0])
return -1
[docs] def get_log(self, author: str, from_seq: int = 0, limit: int = 100) -> list[P2PandaOperation]:
"""Return ordered operations for an author from from_seq onwards."""
clean_author = author.lower().strip()
with self._lock:
rows = self._all(
"SELECT " + _COLS + " FROM p2panda_operations "
"WHERE author = ? AND seq_num >= ? ORDER BY seq_num ASC LIMIT ?",
(clean_author, int(from_seq), max(1, min(int(limit), 100_000))),
)
return [self._row_to_op(r) for r in rows]
[docs] def count_operations(self) -> int:
"""Return total count of operations across all authors."""
with self._lock:
row = self._one("SELECT COUNT(*) AS total FROM p2panda_operations")
return int(row[0]) if row else 0
[docs] def list_authors(self) -> list[str]:
"""Return a sorted list of unique authors with operations in store."""
with self._lock:
return [str(r[0]) for r in
self._all("SELECT DISTINCT author FROM p2panda_operations ORDER BY author ASC")]
[docs] def close(self) -> None:
super().close()