File size: 38,962 Bytes
98c8696
 
 
dc31bad
 
98c8696
 
0d5fa3c
 
 
98c8696
 
 
 
a5981db
98c8696
 
 
 
043da5c
98c8696
 
81a8217
98c8696
 
 
043da5c
dc31bad
 
 
 
 
0d5fa3c
 
dc31bad
 
 
 
 
 
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
98c8696
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
7f72224
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
81a8217
 
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
043da5c
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
98c8696
 
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
81a8217
98c8696
 
a5981db
98c8696
 
 
 
b2aa446
7f72224
 
b2aa446
81a8217
98c8696
 
81a8217
 
b2aa446
 
 
81a8217
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
0d5fa3c
 
 
 
81a8217
 
 
 
 
7f72224
 
 
 
81a8217
 
 
 
 
 
 
 
 
 
 
98c8696
 
0d5fa3c
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
0d5fa3c
 
 
 
 
 
 
 
 
 
 
98c8696
 
 
 
 
 
 
c4a1388
 
 
 
 
 
 
 
 
 
98c8696
 
 
 
 
 
 
 
 
 
 
dc31bad
 
 
 
 
 
 
 
 
 
 
 
98c8696
dc31bad
98c8696
 
 
 
 
 
0d5fa3c
 
 
 
 
 
dc31bad
 
 
 
 
 
 
 
 
 
98c8696
 
043da5c
dc31bad
 
 
 
 
 
0d5fa3c
 
 
 
 
 
 
 
 
 
 
98c8696
 
 
dc31bad
 
 
 
 
 
 
 
98c8696
 
 
 
 
b2aa446
98c8696
 
043da5c
 
 
 
98c8696
 
 
 
043da5c
 
 
 
 
 
 
 
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
98c8696
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
a5981db
 
7f72224
98c8696
 
 
a5981db
98c8696
 
 
 
81a8217
 
 
 
c72f0a1
 
 
 
 
a5981db
c72f0a1
 
 
a5981db
98c8696
 
 
 
 
 
 
 
7f72224
 
 
 
98c8696
 
 
 
 
 
 
81a8217
b2aa446
 
 
 
 
98c8696
 
81a8217
 
 
0d5fa3c
 
81a8217
 
 
0d5fa3c
81a8217
 
 
 
 
 
 
 
 
98c8696
81a8217
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
043da5c
 
98c8696
043da5c
 
 
 
 
 
 
 
 
 
 
c4a1388
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
043da5c
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
98c8696
0d5fa3c
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
dc31bad
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
"""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']