loopable / platform /core /activity_log.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
c4a1388 verified
Raw History Blame Contribute Delete
39 kB
"""Tenant activity in an append-only, indexed PostgreSQL table with calendar-month retention."""
from calendar import monthrange
from contextlib import contextmanager
from contextvars import ContextVar
from datetime import datetime, timezone
import json
import hashlib
import heapq
from itertools import islice
import re
DATABASE_KEY = 'ut_activity'
SYSTEM_KIND = 'activity'
KINDS = frozenset(('record', 'field', 'cell', 'file', 'app', 'view', 'view_as'))
OBJECT_TYPES = frozenset(('database', 'workbook', 'file', 'app'))
CHANGES_MAX_BYTES = 4096
RETENTION_MONTHS = 13
PRUNE_BATCH = 250
MAX_PAGE = 10000
MAX_SCAN = 10000
TABLE_NAME = 'activity_events'
OUTBOX_KEY = '_activity_outbox'
_COLUMNS = ('id', 'occurred_at', 'kind', 'object_type', 'object_key', 'object_id',
'actor', 'via', 'on_behalf_of', 'changes', 'changes_truncated',
'changes_original_bytes')
_decode_json = json.JSONDecoder().decode
_READ_SCOPE = ContextVar('activity_read_scope', default=None)
@contextmanager
def read_scope(rt):
"""Share committed outbox input and bounded history pages within one read-only request."""
state = {'runtime': rt, 'backend': None, 'outbox': None, 'now': None,
'pending': [], 'histories': {}}
token = _READ_SCOPE.set(state)
try:
yield
finally:
_READ_SCOPE.reset(token)
def _json(value):
return json.dumps(value, ensure_ascii=False, allow_nan=False, sort_keys=True)
def _now():
return datetime.now(timezone.utc)
def _time(value):
if isinstance(value, str):
value = datetime.fromisoformat(value.replace('Z', '+00:00'))
if not isinstance(value, datetime) or value.tzinfo is None:
raise ValueError('Use a timezone-aware activity timestamp.')
return value.astimezone(timezone.utc)
def retention_cutoff(now=None):
"""Return the same UTC day and time thirteen calendar months earlier, clamping month ends."""
now = _time(now) if now is not None else _now()
year, month0 = divmod(now.year * 12 + now.month - 1 - RETENTION_MONTHS, 12)
month = month0 + 1
return now.replace(year=year, month=month, day=min(now.day, monthrange(year, month)[1]))
def _text(value, name, *, optional=False):
if optional and value is None:
return None
if not isinstance(value, str) or not value.strip() or len(value.encode('utf-8')) > 1024:
raise ValueError(f'{name} must be a nonempty string of at most 1024 bytes.')
if '\x00' in value:
raise ValueError(f'{name} cannot contain a null character.')
return value
def _object(value, *, allow_key_only=False):
if not isinstance(value, dict):
raise ValueError('object must name one database, workbook, file or app.')
types = set(value) & OBJECT_TYPES
if len(types) != 1 or set(value) - types - {'id'}:
raise ValueError('object must name exactly one object type and its id.')
kind = next(iter(types))
key = _text(value[kind], 'object key')
if 'id' not in value:
if allow_key_only:
return kind, key, None
raise ValueError('object.id is required.')
identity = value['id']
scalar = isinstance(identity, (str, int)) and not isinstance(identity, bool)
addresses = (kind == 'workbook' and isinstance(identity, list) and bool(identity)
and all(isinstance(address, str) and address for address in identity))
if not (scalar or addresses) or identity == '':
raise ValueError('object.id must be a row, path, key or workbook address list.')
encoded = _json(identity)
if len(encoded.encode('utf-8')) > 8192 or '\\u0000' in encoded:
raise ValueError('object.id exceeds 8192 bytes or contains a null character.')
return kind, key, encoded
def _bounded_changes(changes, *, cell_edit=False):
if changes is None:
changes = {}
if not isinstance(changes, dict):
raise ValueError('changes must map field keys to before and after values.')
for key, pair in changes.items():
if not isinstance(key, str) or not isinstance(pair, dict) or set(pair) != {'before', 'after'}:
raise ValueError('Each change needs a string key, before and after.')
raw = _json(changes)
if '\\u0000' in raw:
raise ValueError('changes cannot contain a null character.')
original_bytes = len(raw.encode('utf-8'))
from core import row_cipher
if any(row_cipher.is_sealed(value) for pair in changes.values() for value in pair.values()):
return json.loads(raw), False, original_bytes
if cell_edit or original_bytes <= CHANGES_MAX_BYTES:
return json.loads(raw), False, original_bytes
kept = {}
for key, pair in changes.items():
candidate = {**kept, key: pair}
if len(_json(candidate).encode('utf-8')) <= CHANGES_MAX_BYTES:
kept = candidate
continue
# Keep bounded previews when a single before/after pair exceeds the remaining space.
preview = {side: _json(value) for side, value in pair.items()}
for width in (512, 256, 128, 64, 0):
clipped = {side: value[:width] + ' [truncated]' for side, value in preview.items()}
candidate = {**kept, key: clipped}
if len(_json(candidate).encode('utf-8')) <= CHANGES_MAX_BYTES:
kept = candidate
break
break
return json.loads(_json(kept)), True, original_bytes
def definition(stored=None):
"""Expose a locked database definition without loading or storing event rows."""
fields = [
('occurred_at', 'When', 'date'), ('kind', 'Kind', 'text'),
('object', 'Object', 'json'), ('by', 'By', 'text'), ('via', 'Via', 'text'),
('on_behalf_of', 'On behalf of', 'text'), ('changes', 'Changes', 'json'),
('changes_truncated', 'Changes truncated', 'checkbox'),
('changes_original_bytes', 'Original change bytes', 'int'),
]
base = [{'key': key, 'label': label, 'type': kind} for key, label, kind in fields]
reserved = {field['key'] for field in base}
stored = ({key: value for key, value in stored.items() if key != OUTBOX_KEY}
if isinstance(stored, dict) else {})
extras = [dict(field) for field in stored.get('fields', [])
if isinstance(field, dict) and field.get('key') not in reserved]
return {**stored, 'key': DATABASE_KEY, 'label': 'Activity', 'source': 'Activity',
'systemKind': SYSTEM_KIND, 'createdBy': 'system', 'recordMode': 'automation',
'readThrough': True, 'fields': base + extras, 'rows': {}}
class FixtureAdapter:
"""Explicit offline adapter base, never selected as a fallback for an unbound runtime."""
def _backend(rt):
if rt is None:
raise ValueError('Activity requires an explicitly bound tenant runtime.')
bound = rt._store() if callable(getattr(rt, '_store', None)) else rt
if isinstance(bound, FixtureAdapter):
return bound
from core import store_pg
if not isinstance(bound, store_pg.PgStore):
raise TypeError('Activity requires a bound PgStore or an explicit offline fixture.')
if getattr(rt, 'store_namespace', ''):
raise ValueError('Activity requires a dedicated tenant schema, not a shared namespace.')
key = getattr(rt, 'key', bound.tenant_slug)
if key != bound.tenant_slug:
raise ValueError('Activity runtime and store tenant do not match.')
adapter = getattr(bound, '_activity_log_backend', None)
if adapter is None:
adapter = PgActivityStore(bound)
bound._activity_log_backend = adapter
if adapter.tenant_slug != bound.tenant_slug:
raise ValueError('Activity store binding changed.')
return adapter
def _event(values, *, decoded_json=None):
(identity, occurred_at, kind, object_type, object_key, obj_id, actor, via,
on_behalf_of, changes, truncated, original_bytes) = values
identity = int(identity)
if decoded_json is None:
obj_id = _decode_json(obj_id)
changes = _decode_json(changes) if isinstance(changes, str) else changes
else:
obj_id, changes = decoded_json
return {'id': identity, 'pid': identity,
'occurred_at': _time(occurred_at).isoformat(), 'kind': kind,
'object': {object_type: object_key, 'id': obj_id},
'by': actor, 'via': via, 'on_behalf_of': on_behalf_of,
'changes': changes,
'changes_truncated': bool(truncated),
'changes_original_bytes': original_bytes}
def seal_delivery(rt, entry, *, definition=None):
"""Seal an outbox intent before its caller commits it, preserving delivery identity."""
from core import history_rows
tenant = getattr(rt, 'key', None) or getattr(rt, 'tenant_slug', None)
table = entry.get('object', {}).get('database')
if not table or not tenant or entry.get('kind') == 'field':
return dict(entry)
return {**entry, 'changes': history_rows.cipher_changes(
tenant, table, entry.get('changes') or {}, definition=definition)}
def _open_event(rt, event):
from core import history_rows
tenant = getattr(rt, 'key', None) or getattr(rt, 'tenant_slug', None)
table = event.get('object', {}).get('database')
if table and tenant and event.get('kind') != 'field':
return {**event, 'changes': history_rows.cipher_changes(
tenant, table, event.get('changes') or {}, opening=True)}
return event
def seal_copy_batch(rt, events, outbox, *, backup_path, definitions=None):
"""Prepare Activity and pending delivery copies with an authenticated encrypted backup."""
from core import blob_envelope
tenant = getattr(rt, 'key', None) or getattr(rt, 'tenant_slug', None)
events = list(events)
backup = blob_envelope.seal(tenant, backup_path, _json({'events': events, 'outbox': outbox}).encode(),
required=True)
def seal(entry):
table = entry.get('object', {}).get('database')
return seal_delivery(rt, entry, definition=(definitions or {}).get(table))
sealed_events = [seal(event) for event in events]
sealed_outbox = {key: seal(entry) for key, entry in outbox.items()}
if ([_open_event(rt, event) for event in sealed_events]
!= [_open_event(rt, event) for event in events]
or {key: _open_event(rt, entry) for key, entry in sealed_outbox.items()}
!= {key: _open_event(rt, entry) for key, entry in outbox.items()}):
raise ValueError('Personal Activity batch differs after encryption.')
return sealed_events, sealed_outbox, backup
def append(rt, *, kind, object, by, via=None, on_behalf_of=None, changes=None, _delivery=None):
"""Append one C5 event and remove at most PRUNE_BATCH expired events in the same transaction."""
if kind not in KINDS:
raise ValueError('kind must be record, field, cell, file, app, view or view_as.')
object_type, object_key, object_id = _object(object)
by = _text(by, 'by')
via = _text(via, 'via', optional=True)
on_behalf_of = _text(on_behalf_of, 'on_behalf_of', optional=True)
changes = _open_event(rt, {'kind': kind, 'object': object, 'changes': changes or {}})['changes']
bounded, truncated, original_bytes = _bounded_changes(
changes, cell_edit=kind == 'cell' or bool(_delivery and _delivery.get('_cell_edit')))
bounded = seal_delivery(rt, {'kind': kind, 'object': object, 'changes': bounded})['changes']
now = _time(_delivery['occurred_at']) if _delivery else _now()
values = (now.isoformat(), kind, object_type, object_key, object_id, by, via,
on_behalf_of, _json(bounded), truncated, original_bytes)
backend = _backend(rt)
if _delivery is not None:
return _open_event(rt, backend.insert(values, retention_cutoff().isoformat(), PRUNE_BATCH,
delivery=(_delivery['key'], _delivery['version'])))
return _open_event(rt, backend.insert(values, retention_cutoff(now).isoformat(), PRUNE_BATCH))
def drain(rt, *, keys=None):
"""Replay committed document events and acknowledge only the delivered version."""
_backend(rt) # Validate the tenant before reading or acknowledging its private queue.
from core import user_tables
while isinstance(rt, user_tables._Lent):
rt = rt._runtime
target = rt._store() if callable(getattr(rt, '_store', None)) else rt
if callable(getattr(rt, 'update', None)):
target = rt
if isinstance(target, FixtureAdapter) and not callable(getattr(target, 'get', None)):
return 0
reader = getattr(target, 'get_entry', None)
table = (reader(user_tables.STORE_KEY, DATABASE_KEY) if callable(reader)
else (target.get(user_tables.STORE_KEY) or {}).get(DATABASE_KEY)) or {}
pending = table.get(OUTBOX_KEY) or {}
delivered = {}
try:
# A successful writer also retries older committed deliveries left by failed writers.
ordered = sorted(pending, key=lambda key: key not in keys) if keys is not None else pending
for key in ordered:
entry = pending[key]
append(rt, **entry)
delivered[key] = entry['_delivery']['version']
finally:
if delivered:
def acknowledge(document):
# Delivery priority differs by writer. Lock every delivered row before reading any.
lock_records = getattr(document, 'lock_records', None)
if callable(lock_records):
lock_records({DATABASE_KEY: sorted(delivered)})
queue = (document.get(DATABASE_KEY) or {}).get(OUTBOX_KEY) or {}
for key, version in delivered.items():
entry = queue.get(key)
if entry and entry['_delivery']['version'] == version:
del queue[key]
if not queue and isinstance(document.get(DATABASE_KEY), dict):
document[DATABASE_KEY].pop(OUTBOX_KEY, None)
return document
target.update(user_tables.STORE_KEY, acknowledge, flush='sync')
return len(delivered)
def _delivery_id(key):
"""Reserve an exact JSON integer for a delivery before its first persistent insert."""
digest = hashlib.sha256(_text(key, 'delivery key').encode()).digest()
return (1 << 52) | (int.from_bytes(digest[:8], 'big') & ((1 << 52) - 1))
def _outbox(rt):
"""Read only this tenant's private committed Activity entry."""
from core import user_tables
while isinstance(rt, user_tables._Lent):
rt = rt._runtime
target = rt._store() if callable(getattr(rt, '_store', None)) else rt
if callable(getattr(rt, 'get_entry', None)):
target = rt
reader = getattr(target, 'get_entry', None)
if callable(reader):
table = reader(user_tables.STORE_KEY, DATABASE_KEY) or {}
elif isinstance(target, FixtureAdapter) and not callable(getattr(target, 'get', None)):
return {}
else:
table = (target.get(user_tables.STORE_KEY) or {}).get(DATABASE_KEY) or {}
return table.get(OUTBOX_KEY) or {}
def _pending_page(backend, pending, *, target, actor, since, until, before, limit):
"""Project a bounded pending page with persistent delivery identities and version deduplication."""
def events():
entries = iter(pending.items())
while chunk := list(islice(entries, 256)):
delivered = backend.deliveries([key for key, _ in chunk])
occupied = {receipt['id']: key for key, (_, receipt) in delivered.items()}
for key, entry in chunk:
delivery = entry['_delivery']
if delivery['key'] != key:
raise ValueError('Activity delivery key does not match its committed intent.')
prior = delivered.get(key)
stamp = _time(delivery['occurred_at']).isoformat()
kind, name, identity = _object(entry['object'])
event_id = prior[1]['id'] if prior is not None else _delivery_id(key)
if event_id in occupied and occupied[event_id] != key:
raise ValueError('Activity delivery identity conflicts with another event.')
if (not since <= stamp <= until or (actor is not None and entry['by'] != actor)
or (target is not None and (kind, name) != target[:2])
or (target is not None and target[2] is not None and identity != target[2])):
continue
if before is not None and (stamp, event_id) >= before:
continue
if prior is not None and prior[0] >= delivery['version']:
yield prior[1]
continue
changes, truncated, original_bytes = _bounded_changes(
entry.get('changes'), cell_edit=entry['kind'] == 'cell' or delivery.get('_cell_edit', False))
yield _event((event_id, stamp, entry['kind'], kind, name, identity,
entry['by'], entry.get('via'), entry.get('on_behalf_of'),
changes, truncated, original_bytes),
decoded_json=(_decode_json(identity), changes))
page = heapq.nlargest(limit + 1, events(), key=lambda event: (event['occurred_at'], event['id']))
if len({event['id'] for event in page}) != len(page):
raise ValueError('Activity pending deliveries have conflicting identities.')
return EventPage(page[:limit], has_more=len(page) > limit)
def prune(rt, *, limit=PRUNE_BATCH, now=None):
"""Remove a bounded oldest-first batch strictly older than thirteen calendar months."""
if type(limit) is not int or not 1 <= limit <= MAX_SCAN:
raise ValueError(f'limit must be between 1 and {MAX_SCAN}.')
return _backend(rt).prune(retention_cutoff(now).isoformat(), limit)
def usage_counts(rt, *, since, until, now, kinds, actor=None):
"""Aggregate tenant-wide metadata, keeping the ordinary overlay reader for pending deliveries."""
backend = _backend(rt)
if not isinstance(backend, PgActivityStore) or _outbox(rt):
return None
now = _time(now)
return backend.usage_counts(since=max(_time(since), retention_cutoff(now)).isoformat(),
until=_time(until).isoformat(), now=now.isoformat(), kinds=kinds, actor=actor)
def read(rt, *, can_read_object, object=None, by=None, since=None, until=None,
cursor=None, limit=100, scan_limit=MAX_SCAN):
"""Read an indexed page, authorizing each object before returning any event or change value."""
if not callable(can_read_object):
raise PermissionError('An object permission resolver is required for activity reads.')
if type(limit) is not int or not 1 <= limit <= MAX_PAGE:
raise ValueError(f'limit must be between 1 and {MAX_PAGE}.')
if type(scan_limit) is not int or not limit <= scan_limit <= MAX_SCAN:
raise ValueError(f'scan_limit must be between limit and {MAX_SCAN}.')
target = _object(object, allow_key_only=True) if object is not None else None
actor = _text(by, 'by') if by is not None else None
scope = _READ_SCOPE.get()
scope = scope if scope is not None and scope['runtime'] is rt else None
backend = _backend(rt)
if scope is not None:
if scope['backend'] is None:
scope['backend'] = backend
elif scope['backend'] is not backend:
scope = None
now = scope['now'] if scope is not None and scope['now'] is not None else _now()
floor = retention_cutoff(now)
whole_history = (target is not None and target[0] == 'workbook' and target[2] is None
and by is None and since is None and until is None and cursor is None)
since = max(_time(since), floor) if since is not None else floor
until = _time(until) if until is not None else now
before = None
if cursor is not None:
if (not isinstance(cursor, (list, tuple)) or len(cursor) != 2
or type(cursor[1]) is not int or cursor[1] < 1):
raise ValueError('Use the next_cursor returned by the activity reader.')
before = (_time(cursor[0]).isoformat(), cursor[1])
if scope is not None:
if scope['outbox'] is None:
scope['outbox'], scope['now'] = _outbox(rt), now
pending = scope['outbox']
else:
pending = _outbox(rt)
prefetched = None
if scope is not None and whole_history:
workbook = target[1]
if workbook not in scope['histories'] and workbook in scope['pending']:
fetch = getattr(backend, 'workbook_pages', None)
if callable(fetch):
wanted = [workbook] + [key for key in scope['pending'] if key != workbook][:15]
scope['histories'] = fetch(wanted, since=since.isoformat(), until=until.isoformat(), limit=256)
scope['pending'] = [key for key in scope['pending'] if key not in wanted]
prefetched = scope['histories'].pop(workbook, None)
rows, scanned, exhausted = [], 0, False
while scanned < scan_limit and len(rows) < limit:
batch_limit = min(max(256, limit), scan_limit - scanned)
if prefetched is not None and batch_limit >= 256:
batch, prefetched, batch_limit = prefetched, None, 256
else:
batch = backend.page(target=target, actor=actor, since=since.isoformat(),
until=until.isoformat(), before=before,
limit=batch_limit)
if pending:
overlay = _pending_page(backend, pending, target=target, actor=actor,
since=since.isoformat(), until=until.isoformat(),
before=before, limit=batch_limit)
merged = {event['id']: event for event in batch}
merged.update((event['id'], event) for event in overlay)
ordered = heapq.nlargest(batch_limit + 1, merged.values(),
key=lambda event: (event['occurred_at'], event['id']))
batch = EventPage(ordered[:batch_limit], has_more=(len(ordered) > batch_limit
or getattr(batch, 'has_more', len(batch) == batch_limit)
or overlay.has_more))
if not batch:
exhausted = True
break
if scope is not None and not whole_history:
# Queue ids only. A nested history read fetches at most sixteen bounded pages.
known = set(scope['pending']) | scope['histories'].keys()
for event in batch:
workbook = event['object'].get('workbook')
if workbook is not None and workbook not in known:
scope['pending'].append(workbook)
known.add(workbook)
for event in batch:
before = (event['occurred_at'], event['id'])
scanned += 1
# The application supplies its current object, row, field and BU permission wall.
if can_read_object(event['object']) is True:
rows.append(_open_event(rt, event))
if len(rows) >= limit or scanned >= scan_limit:
break
if event is batch[-1] and (len(batch) < batch_limit
or getattr(batch, 'has_more', None) is False):
exhausted = True
break
# The application must wrap this internal position in its session-bound opaque cursor.
return {'records': rows, 'next_cursor': None if exhausted else before}
class EventPage(list):
"""Carry storage exhaustion without exposing or consuming the next event."""
def __init__(self, events=(), *, has_more=False):
super().__init__(events)
self.has_more = has_more
class PgActivityStore:
"""Indexed activity storage inside the bound PgStore schema, with guarded scratch measurement."""
def __init__(self, bound_store, *, scratch=None):
from core import store_pg
if not isinstance(bound_store, store_pg.PgStore):
raise TypeError('A PgStore is required.')
self.tenant_slug = bound_store.tenant_slug
self.schema = store_pg._schema_for(self.tenant_slug)
self.name = TABLE_NAME if scratch is None else scratch
if scratch is not None and not re.fullmatch(r'activity_probe_[a-f0-9]{16}', scratch):
raise ValueError('Use a distinct activity_probe_<16 hex digits> scratch table.')
if not re.fullmatch(r'[a-zA-Z0-9_]+', self.schema):
raise ValueError('Invalid tenant schema.')
self.table = f'"{self.schema}"."{self.name}"'
self._ready = False
def _write_guard(self):
from core import store_pg
store_pg.check_write(self.tenant_slug, 'write activity log')
def _connection(self):
from core import store_pg
store_pg._effective_slug(self.tenant_slug)
return store_pg._pool().connection()
def _exists(self, con):
return con.execute('SELECT to_regclass(%s)', (self.table,)).fetchone()[0] is not None
def ensure(self):
self._write_guard()
if self._ready:
return
with self._connection() as con:
from core import store_pg
if store_pg._CALLER_TRANSACTION.get() is not None and self._exists(con):
columns = con.execute('SELECT attname FROM pg_attribute WHERE attrelid = %s::regclass '
'AND attnum > 0 AND NOT attisdropped', (self.table,)).fetchall()
indexes = con.execute('SELECT indexname FROM pg_indexes WHERE schemaname = %s '
'AND tablename = %s', (self.schema, self.name)).fetchall()
constraint = con.execute('SELECT pg_get_constraintdef(oid) FROM pg_constraint '
'WHERE conrelid = %s::regclass AND conname = %s',
(self.table, f'{self.name}_kind_check')).fetchone()
needed = {f'{self.name}_{suffix}' for suffix in (
'delivery', 'time', 'object_time', 'object_id_time', 'person_time')}
if (set(_COLUMNS) | {'delivery_key', 'delivery_version'} <= {r[0] for r in columns}
and needed <= {r[0] for r in indexes} and constraint
and "'view_as'" in constraint[0]):
return
con.execute('SELECT pg_advisory_xact_lock(hashtextextended(%s, 0))', (self.table,))
con.execute(f'''CREATE TABLE IF NOT EXISTS {self.table} (
id BIGSERIAL PRIMARY KEY, occurred_at TIMESTAMPTZ NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('record','field','cell','file','app','view','view_as')),
object_type TEXT NOT NULL, object_key TEXT NOT NULL, object_id TEXT NOT NULL,
actor TEXT NOT NULL, via TEXT, on_behalf_of TEXT,
changes JSONB NOT NULL, changes_truncated BOOLEAN NOT NULL,
changes_original_bytes INTEGER NOT NULL)''')
con.execute(f'ALTER TABLE {self.table} ADD COLUMN IF NOT EXISTS delivery_key TEXT')
con.execute(f'ALTER TABLE {self.table} ADD COLUMN IF NOT EXISTS delivery_version INTEGER')
con.execute(f'CREATE UNIQUE INDEX IF NOT EXISTS "{self.name}_delivery" '
f'ON {self.table} (delivery_key)')
constraint = f'{self.name}_kind_check'
prior = con.execute(
'SELECT pg_get_constraintdef(oid) FROM pg_constraint '
'WHERE conrelid = %s::regclass AND conname = %s',
(self.table, constraint)).fetchone()
if prior is None or "'view_as'" not in prior[0]:
con.execute(f'ALTER TABLE {self.table} DROP CONSTRAINT IF EXISTS "{constraint}"')
# The former constraint already admitted every existing event kind.
con.execute(f'ALTER TABLE {self.table} ADD CONSTRAINT "{constraint}" '
"CHECK (kind IN ('record','field','cell','file','app','view','view_as')) NOT VALID")
for suffix, columns in (
('time', 'occurred_at DESC, id DESC'),
('object_time', 'object_type, object_key, occurred_at DESC, id DESC'),
('object_id_time', 'object_type, object_key, md5(object_id), occurred_at DESC, id DESC'),
('person_time', 'actor, occurred_at DESC, id DESC'),
):
con.execute(f'CREATE INDEX IF NOT EXISTS "{self.name}_{suffix}" '
f'ON {self.table} ({columns})')
# A caller rollback can remove newly created indexes and the table itself.
from core import store_pg
if store_pg._CALLER_TRANSACTION.get() is None:
self._ready = True
def _prune(self, con, cutoff, limit):
return con.execute(f'''DELETE FROM {self.table} WHERE id IN (
SELECT id FROM {self.table} WHERE occurred_at < %s
ORDER BY occurred_at, id LIMIT %s FOR UPDATE SKIP LOCKED)''',
(cutoff, limit)).rowcount
def insert(self, values, cutoff, limit, *, delivery=None):
if values[2] == 'database' and values[1] != 'field':
from core import history_rows
changes = json.loads(values[8]) if isinstance(values[8], str) else values[8]
values = (*values[:8], _json(history_rows.cipher_changes(
self.tenant_slug, values[3], changes)), *values[9:])
self.ensure()
with self._connection() as con:
columns = _COLUMNS[1:]
conflict = ''
if delivery is not None:
columns = ('id', *columns)
values = (_delivery_id(delivery[0]), *values)
columns += ('delivery_key', 'delivery_version')
values = (*values, *delivery)
assignments = ', '.join(f'{column} = EXCLUDED.{column}' for column in columns
if column not in ('id', 'delivery_key'))
conflict = (f' ON CONFLICT (delivery_key) DO UPDATE SET {assignments} '
f'WHERE {self.table}.delivery_version < EXCLUDED.delivery_version')
stored = con.execute(
f'INSERT INTO {self.table} ({", ".join(columns)}) '
f'VALUES ({", ".join(["%s"] * len(columns))}){conflict} '
f'RETURNING {", ".join(_COLUMNS)}', values).fetchone()
if stored is None:
stored = con.execute(f'SELECT {", ".join(_COLUMNS)} FROM {self.table} '
'WHERE delivery_key = %s', (delivery[0],)).fetchone()
self._prune(con, cutoff, limit)
return _event(stored)
def prune(self, cutoff, limit):
self._write_guard()
with self._connection() as con:
return self._prune(con, cutoff, limit) if self._exists(con) else 0
@staticmethod
def _where(*, target, actor, since, until, before):
clauses = ['occurred_at >= %s', 'occurred_at <= %s']
args = [since, until]
if target is not None:
clauses += ['object_type = %s', 'object_key = %s']
args += list(target[:2])
if target[2] is not None:
clauses.append('md5(object_id) = md5(%s)')
args.append(target[2])
# The cast preserves the hash index predicate when SQLite propagates constants.
clauses.append('CAST(object_id AS TEXT) = %s')
args.append(target[2])
if actor is not None:
clauses.append('actor = %s')
args.append(actor)
if before is not None:
clauses.append('(occurred_at, id) < (%s, %s)')
args += list(before)
return ' AND '.join(clauses), args
def page_query(self, *, target, actor, since, until, before, limit):
"""Fetch bounded event values and one indexed existence bit from the same snapshot."""
where, args = self._where(target=target, actor=actor, since=since, until=until, before=before)
columns = ', '.join('CAST(changes AS TEXT) AS changes' if col == 'changes' else col
for col in _COLUMNS)
# Only the full page's last row probes the next indexed position, without loading its values.
sql = ('SELECT page.*, CASE WHEN row_number() OVER (ORDER BY occurred_at DESC, id DESC) = %s '
f'THEN EXISTS (SELECT 1 FROM {self.table} WHERE {where} '
'AND (occurred_at, id) < (page.occurred_at, page.id)) ELSE NULL END AS has_more '
f'FROM (SELECT {columns} FROM {self.table} WHERE {where} '
'ORDER BY occurred_at DESC, id DESC LIMIT %s) page '
'ORDER BY occurred_at DESC, id DESC')
return sql, (limit, *args, *args, limit)
def usage_counts(self, *, since, until, now, kinds, actor=None):
"""Group exact UTC day, actor and kind counts without fetching event values."""
selected = sorted(set(kinds) & {'record', 'field'})
if not selected:
return []
where, args = self._where(target=None, actor=actor, since=since, until=now, before=None)
where += ' AND occurred_at < %s AND kind IN (' + ', '.join('%s' for _ in selected) + ')'
args += [until, *selected]
try:
with self._connection() as con:
return con.execute(
"SELECT to_char(occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD'), actor, kind, count(*) "
f'FROM {self.table} WHERE {where} GROUP BY 1, 2, 3', args).fetchall()
except Exception as exc:
if getattr(exc, 'sqlstate', None) != '42P01':
raise
return []
def page(self, *, target, actor, since, until, before, limit):
sql, args = self.page_query(target=target, actor=actor, since=since, until=until,
before=before, limit=limit)
try:
with self._connection() as con:
values = con.execute(sql, args).fetchall()
except Exception as exc:
# Only PostgreSQL's undefined-table state means no activity; other failures propagate.
if getattr(exc, 'sqlstate', None) != '42P01':
raise
return EventPage()
events = EventPage(has_more=bool(values and values[-1][-1]))
# Bound temporary JSON text while sharing immutable keys within each decoded batch.
for start in range(0, len(values), 256):
batch = values[start:start + 256]
decoded = _decode_json('[' + ','.join(part for row in batch
for part in (row[5], row[9])) + ']')
if len(decoded) != 2 * len(batch):
raise ValueError('Activity JSON batch does not match its rows.')
for row, obj_id, changes in zip(batch, decoded[::2], decoded[1::2]):
events.append(_event(row[:-1], decoded_json=(obj_id, changes)))
return events
def deliveries(self, keys):
"""Resolve at most 256 committed delivery receipts without changing the store."""
if not keys or len(keys) > 256:
raise ValueError('Read between 1 and 256 delivery receipts.')
columns = ', '.join('CAST(changes AS TEXT) AS changes' if col == 'changes' else col
for col in _COLUMNS)
try:
with self._connection() as con:
reserved = [_delivery_id(key) for key in keys]
placeholders = ', '.join(['%s'] * len(keys))
values = con.execute(f'SELECT {columns}, delivery_key, delivery_version '
f'FROM {self.table} WHERE delivery_key IN '
f'({placeholders}) OR id IN ({placeholders})',
[*keys, *reserved]).fetchall()
except Exception as exc:
if getattr(exc, 'sqlstate', None) != '42P01':
raise
return {}
return {row[-2]: (row[-1], _event(row[:-2])) for row in values}
def workbook_pages(self, workbooks, *, since, until, limit):
"""Read bounded first history pages through the existing tenant and object-time index."""
if not 1 <= len(workbooks) <= 16 or not 1 <= limit <= 256:
raise ValueError('Workbook history prefetch exceeds its read bound.')
columns = ', '.join('CAST(changes AS TEXT) AS changes' if col == 'changes' else col
for col in _COLUMNS)
sql = (f'SELECT wanted.workbook, page.* FROM unnest(%s::text[]) AS wanted(workbook) '
f'CROSS JOIN LATERAL (SELECT {columns} FROM {self.table} '
"WHERE object_type = 'workbook' AND object_key = wanted.workbook "
'AND occurred_at >= %s AND occurred_at <= %s '
'ORDER BY occurred_at DESC, id DESC LIMIT %s) page '
'ORDER BY wanted.workbook, page.occurred_at DESC, page.id DESC')
pages = {key: EventPage() for key in workbooks}
try:
with self._connection() as con:
values = con.execute(sql, (list(workbooks), since, until, limit + 1)).fetchall()
except Exception as exc:
if getattr(exc, 'sqlstate', None) != '42P01':
raise
return pages
for workbook, *row in values:
page = pages[workbook]
if len(page) == limit:
page.has_more = True
else:
page.append(_event(row))
return pages
def storage_stats(self):
with self._connection() as con:
if not self._exists(con):
return {'events': 0, 'table_bytes': 0, 'index_bytes': 0, 'total_bytes': 0}
sizes = con.execute('SELECT pg_table_size(%s::regclass), pg_indexes_size(%s::regclass), '
'pg_total_relation_size(%s::regclass)', (self.table,) * 3).fetchone()
count = con.execute(f'SELECT count(*) FROM {self.table}').fetchone()[0]
return dict(zip(('events', 'table_bytes', 'index_bytes', 'total_bytes'), (count, *sizes)))
def drop_scratch(self):
"""Drop only a dedicated synthetic probe table and verify its absence."""
if not re.fullmatch(r'activity_probe_[a-f0-9]{16}', self.name):
raise PermissionError('The activity log cannot be dropped through this API.')
self._write_guard()
with self._connection() as con:
con.execute(f'DROP TABLE IF EXISTS {self.table}')
if self._exists(con):
raise RuntimeError('Activity scratch cleanup failed.')
self._ready = False
return self.storage_stats()['events']