Download platform/core/activity_log.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 39 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/core/activity_log.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/core/activity_log.py
-
curl -L -o activity_log.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/core/activity_log.py
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) | |
| 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 | |
| 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'] | |