database-scalability-review.md

Database Scalability Review

Source Of Truth

Original request for this slice:

Add to TODO also review of scalability of database to possibility of storing hundreds of thousands posts with its contents in groups.

Additional review request:

Review the data scheme in general: whether it is flexible enough, whether it has all required components, how contents and posts are stored, how posts are added to groups, how JSON manifests are generated, and what should be done in that logic overall.

Follow-up review request:

Check cases when different users uploaded content with the same storageId; maybe it needs to be handled more correctly.

Issue: #880 Review database scalability for large groups.

Status

Implementation work is landing slice-by-slice on codex/database-scalability-review. The findings table below is the inventory; this section is the running log of what has shipped so future readers see the doc in context.

Slice	Decision / Finding	Commit
Bug-fix	Wire actor and group context through manifest -> post DB import	3dc8ffd
1	C1: getPostsWhere defaults to status: Published; public API endpoints sanitize visibility-override fields	f518fa0
2	B5: updatePost rejects cross-group groupId changes	e944469
3	B4: drafts are DB-only — manifest/static/encryption only on Published rows	f200c85
4	First Postgres CONCURRENTLY migration: post timeline + post-content indexes	92d4d3a
5	J20: 100k-post Docker fixture + EXPLAIN ANALYZE harness	e42735d
6	Incremental Group.size / availablePostsCount / publishedPostsCount	7eb67aa
7	deletePosts reconciles counters and regenerates the group manifest	529b27c
Style	Code-style guide + ban inline if statements	4384271
8	A1 shared-content seam + reference-counted physical-storage delete	f39d9c3
9	Additive index migrations: content/quota/permission/static-id	c1ab1d4
10	Postgres pool config + rename MysqlDatabase -> PostgresDatabase	c9045bc
11	D8/D9: keyset/cursor pagination on getGroupPosts (with id tie-breaker)	152547e
12	Targeted diff in setPostContents instead of full join reset	40beb07
13	Per-attachment view read from PostsContents join row	461b82c
14	Widen posts.size, groups.size, userContentActions.size, content preview sizes to BIGINT	a97dc82
15	A1 attachment resolver: DB IDs are ownership-checked, manifest IDs import/claim actor-scoped rows, and file-catalog delete preserves referenced content rows	148ee6c
16	P1 local post numbering: getPostLocalId now allocates under a Postgres row lock	d123d56
17	P1 timeline page reads: getGroupPosts / getAllPosts select page IDs first, then hydrate only that bounded page	db3f5d9
18	P2 category feed reads: getCategoryPosts keeps pivot joins in page-ID selection and hydrates only the bounded page	c609766
19	Category feed page selection trims join projection to post id/order plus the required group id alias before hydration	481027e
20	Additive lookup indexes for async operations, auto-actions, pin accounts, static rebind scans, user limits, and category/section pivots	7d3f24b
21	Shared list pagination params clamp negative/oversized paging, sort directions, and unsafe sort column names before Sequelize	94934aa
22	Canonical post create/update DB state is transaction-scoped: local ID allocation, post row, attachments, size, reply count, and group counters	c406e75
23	Canonical post delete DB state is transaction-scoped: tombstone flag, group size/availability counters, and reply/repost counters	76d063a
24	Deleted posts are removed from regenerated group manifest tries, and group-manifest differential scans use lightweight post refs	4fe962e
25	Group-manifest changed/deleted ref scans now iterate in (updatedAt,id) cursor batches instead of fixed large windows	09ad8f8
26	Published posts that move back to draft/unpublished state are removed from regenerated group manifest tries	47f9a27
27	Reply/repost counters are reconciled when posts cross published status boundaries	edad982
28	A1 content identity now keeps (storageId, userId) as a non-unique actor lookup; production restores can contain same-user duplicate storage rows	45ae38f, this PR
29	Additive lookup indexes for tag, mention, and auto-tag future feed/federation surfaces	35b8187
30	Group unread cursors can use (readAt, readPostId) to avoid same-timestamp misses	3a6fab3
31	Category feeds support (publishedAt,id) cursor pages that skip count/offset work	b337dba
32	Social import reversal uses lightweight post refs instead of hydrated timeline rows	c7ea2e9
33	Static-site/RSS generated output uses lightweight post refs and batched hydration	eddf88b
34	Social import reversal scans post refs in cursor batches instead of one fixed window	16b44dc
35	User limits enforce one row per (userId,name) after duplicate cleanup and duplicate-key retry	0bd5698
36	Post attachment positions enforce one row per (postId,position) after duplicate cleanup	978c5e1
37	Group-local post IDs enforce one row per (groupId,localId) after duplicate backfill	11ba53c
38	Social import post source identity enforces one row per (groupId,source,sourceChannelId,sourcePostId) after duplicate cleanup and duplicate-key retry	39b6135
39	File catalog enforces active path uniqueness per user/parent, adds the folder-listing index, retries concurrent path writes through the existing item, and suffixes duplicate generic upload names	a92296e
40	Pin accounts enforce deterministic owner/name lookup with cleanup-backed unique (userId,name) and (groupId,name) indexes	01311bd
41	Migration integrity audit verifies recent migrations, final indexes/types, deduped identities, high-water counters, and broken relations after backup/restore rollout rehearsal	dee10ab
42	Public group/category post feeds use indexed sort allowlists and a lower browsing page-size cap	dd06bfd
43	AutoAction due scans are deterministic/bounded and the cron queue de-dupes already queued or running action IDs inside one node	4417f5a
44	User file-catalog browsing uses endpoint sort allowlists and a lower page-size cap without capping internal folder export/publish scans	c8404d7
45	Category group and section management lists use endpoint sort allowlists and lower page-size caps	15e63cc
46	Static-site and operation-queue user lists use endpoint sort allowlists, default pages, lower caps, and count-only totals	f2e2dd7
47	Invite lists use endpoint sort allowlists, lower caps, and count-only user totals	4dc8547
48	API-key management lists use endpoint sort allowlists and lower page-size caps	a6e11e0
49	Admin user/group/content directory lists use endpoint sort allowlists and lower page-size caps	54bfac5
50	User member/admin/personal-chat group lists use endpoint sort allowlists, lower page-size caps, and count-only totals	1531a60
51	User friend lists use endpoint sort allowlists, lower page-size caps, deterministic ordering, and search-aware totals	cc702c5
52	File-catalog folder publish traverses child rows in bounded batches instead of accidentally publishing only the default page	966ab4e
53	AutoAction management lists use endpoint sort allowlists, lower page-size caps, filter allowlists, count-only totals, and an indexed default user page order	d4543e2
54	Finished async-operation history and linked/orphan queue rows are cleaned on startup in bounded, index-backed retention batches	024554f
55	Creator-owned group listings use a matching (creatorId,type,isDeleted,createdAt,id) index and stable id tie-breakers	3d186c8
56	Object-cache rows key resolved and unresolved storage-path reads by (storageId,resolveProp) instead of blocking one variant with storageId uniqueness	3119f8b
57	Shared content storage/manifest lookups use deterministic helpers, and remote user avatar imports avoid global manifest reuse when an actor is present	26320cc
58	Group and category post feeds accept includeTotal=false so offset callers can skip expensive count queries without switching response shape	b397957
59	AutoAction cron workers atomically claim due rows with expiring DB-backed execution claims before queueing work	28b0cb5
60	Global post listings expose cursor-aware lightweight refs and a batch walker before bounded hydration	b829e67
61	Static-ID hot resolution uses a compact model-sync current-binding table while history remains the audit trail	6b94b31 / be55410
62	Static-ID history compacts stale per-static rows while preserving the current binding	56bd171
63	Upload content rows and upload accounting commit under a locked user-limit re-check	cd95bac
64	Static-rebind candidate scans are capped and ordered oldest-first before the disabled cron is re-enabled	814a02f
65	Reply/repost counter repair is centralized and covers create/update/delete relation changes	9ac56ae / fe138aa
66	Social import source-identity upserts have relation-counter regression coverage	0ede9f5
67	Social import source-identity upserts have group size/availability regression coverage	d22ab9d
68	Group counter repair has drift coverage for size, availability, and local-ID high-water state	f6c2016
69	Source-identity import upserts cover published -> draft reconciliation for counters and manifest removal	9c5266e
70	updatePost treats isDeleted as leaving the public lifecycle, including source-identity import tombstones	4bef0c7
71	Source-identity imports append durable postEvents rows for create/update/delete lifecycle transitions	69d7f7e
72	Ordinary post create/update/delete writes append post_lifecycle events in the same DB transaction	52eb403
73	Remote post manifest imports preserve source/date fields and use the canonical post DB state path	fbb6a48
74	Remote post manifest import retries reuse the active same-group manifest row under the group write lock and refresh the group manifest	322699d
75	Remote group manifest imports iterate missing post refs, preserve manifest local IDs, and skip already-complete groups	de83a34
76	Remote group manifest imports replay removed/replaced refs by tombstoning stale rows, freeing reused remote local IDs, and importing replacements	1e7f882
77	Large fixture and EXPLAIN harness cover category, static/RSS, preview, quota, static-ID, manifest, unread, and cursor timeline hot paths	e87fdff
78	Static-site generated pages render availablePostsCount as the public total while keeping publishedPostsCount as the local-ID high-water mark	0d33253
79	Pin account list endpoints use shared list-param sanitization, owner/name ordering, and lower page caps	8c71c3f
80	Foreign-account API lists use bounded list params while manifest export still includes the full account set	94cf74c
81	Social-network account API lists use bounded list params and preserve legacy social-network filters	63c365b
82	Content-bot API lists use bounded list params and allowlisted ordering	b055ca2
83	Content-bot user-list lookups have a matching production/fresh-schema index	4a84aa4
84	RSS feed rendering uses metadata-only content projection except for the selected feed text body	15afa9a
85	File-catalog default upload folders retry concurrent creation under active path uniqueness	15afa9a
86	Static-site generation streams SSR through current page/post state instead of materializing one final posts array	ded95ef
87	Ownerless remote content import is read-only against existing shared rows and requires an actor before creating new library rows	bc8d88c
88	Public content metadata uses a safe projection and hides private content rows when looked up by numeric DB id	5a35f1f
89	Post attachment resolution returns explicit owner/public/actor import reasons for future share/group/admin policy	221a545
90	RSS feeds default to a smaller item window while explicit archive reads stay capped at the legacy maximum	71660e8
91	Restored-backup migration rehearsal has a guarded command that runs migrations, model sync, and the integrity audit	65692b4
92	Text/json body projection can reuse bounded per-render/feed caches, and static-site rendering reuses converted reply/repost post objects	shipped
93	Generated-output pressure report measures static/RSS body-read uniqueness versus non-text copy work on the fixture or a restored group	shipped
94	Group manifests dual-write a paged post-index sidecar, and remote import can read chunked-only post indexes	fc87ac1
95	Group manifest generation can omit legacy inline posts via an explicit option or post-count limit while retaining the paged postsIndex	d83fb1d
96	Generated-output pressure reports include group-manifest inline/index pressure to guide large-group chunked-only rollout	59fe3c1
97	Category and section aggregate size columns are BIGINT and covered by migration integrity checks	fe6227c
98	Compatibility group-manifest scans persist and reuse a durable (updatedAt,id) post cursor with same-timestamp overlap	0250677
99	Scalability EXPLAIN probes measure the current manifest cursor, static-ID binding, and repair/fallback paths	e628cfb
100	Docker 100k-post synthetic fixture, EXPLAIN, and generated-output pressure pass completed against current probes	bc70761
101	Restored-backup pressure checks have a guarded command mirroring the migration rehearsal safety rails	537f7c2
102	Restored-backup pressure checks can target a specific group by id, and EXPLAIN skips fixture-only probes when restored data lacks sample rows	3cfda57
103	May 15 production dump migration rehearsal passed against disposable Docker Postgres with migration integrity clean	this PR
104	May 15 posts dump exposed legacy null post sizes and stale group size counters; migration backfills them from attached content and the integrity audit now verifies the final state	this PR
105	Group manifest postsIndex regeneration rewrites only touched post-index pages when a previous paged index exists, and the IPLD storage direction is documented in docs/group-manifest-ipld-scalability.md	this PR
106	Group manifests default to chunked-only generation above 1,000 available posts while keeping GROUP_MANIFEST_INLINE_POSTS_LIMIT, per-call inlinePostsLimit, and includeInlinePosts as compatibility overrides	this PR
107	Post/group manifest rebuilds can enter a durable group-derived-state queue with input-hash dedupe, bounded retry attempts, and explicit synchronous escape hatches	this PR
108	Derived-state queue jobs now record post/group manifest and static-directory checkpoint status in async-operation output, retry errors include attempt context, and userAsyncOperations.output is widened to TEXT	this PR
109	Derived-state queue processing is bounded per kick, can run through the interval worker, and stops cleanly with the group module	this PR
110	Restored-data async derived-state rehearsal now has a guarded command that enqueues missing manifest/static repairs through group-derived-state, drains bounded batches, and reruns integrity for rollout evidence	this PR
111	The May 15 posts dump passed the guarded async rehearsal: one missing local post manifest/directory was queued, processed, and final derived-state integrity returned zero issues	this PR
112	Post/group manifest rebuilds now default to the durable group-derived-state queue, with GROUP_DERIVED_STATE_ASYNC=0 or internal asyncDerivedState: false options preserving synchronous escape hatches	this PR
113	New content writes seed a model-sync StorageObject registry keyed by storageId, giving the A2 canonical physical-storage path a safe first table while Content remains the per-user ownership row	this PR
114	Shared public storage/header metadata reads now prefer StorageObject and fall back to deterministic Content rows for old data	this PR
115	Successful remote pins now mark the canonical StorageObject.isPinned row, and file-catalog physical deletion checks that canonical pin bit before unpin/remove while keeping Content.isPinned compatibility	this PR
116	Restored/upgraded databases can reconcile storageObjects from deterministic contents metadata, including legacy Content.isPinned, and restored-backup migration rehearsal runs that repair before the final integrity audit	this PR
117	File-catalog physical deletion now treats current static-ID dynamic bindings, plus latest-history fallback rows for missing current bindings, as derived storage refs	this PR
118	Derived storage reference counting now covers the remaining known storage columns on file-catalog native refs, categories, sections, tags, and mentions	this PR
119	Content/storage delete safety is centralized in database.getContentDeleteSafety, including exclusion for the file-catalog row currently being deleted, so future content delete paths share the same reference-count decision	this PR
120	The centralized delete-safety result now includes content/storage blocker lists with counts, giving delayed-GC jobs and operator checks a reusable explanation for why a row or physical object cannot be removed yet	this PR
121	First storage-space analyzer backend helpers expose overview totals, MIME/type breakdowns, largest content rows, largest catalog files, and largest groups while separating logical content bytes from deduplicated physical storage bytes	this PR
122	AdminRead-protected storage-space API routes now expose the analyzer overview, type breakdown, largest content rows, largest catalog files, and largest groups for the future operator UI	this PR
123	Storage-space analyzer results can now be persisted as model-sync cached snapshots with list limit, duration, timestamp, and refresher user metadata, exposed through AdminRead latest/refresh routes for operator screens	this PR
124	Storage-space snapshot refreshes can now be queued through the shared async-operation queue runner via an AdminRead async refresh route, while the database module owns only the storage-space job payload and snapshot calculation	this PR
125	Storage-space file-catalog folder usage now has an AdminRead drilldown endpoint and cached snapshot slice for root/child folder logical and deduplicated physical bytes	this PR
126	Storage-space group drilldown now lists largest published posts with attachment counts, logical bytes, and per-post deduplicated physical bytes through AdminRead API and cached snapshots	this PR
127	Storage-space analyzer service and AdminRead routes moved into a dedicated storageSpace module, while the database module keeps the synced snapshot model and shared SQL connection only	this PR
128	Storage-space analyzer now exposes DB-visible generated/static output refs by source column, known StorageObject bytes, and unknown DAG refs that still need IPFS traversal or metadata reconciliation	this PR
129	Queued storage-space snapshot refreshes now report staged analyzer progress in the async operation while preserving the parallel query path for synchronous refreshes	this PR
130	Storage-space analysis can now page unknown generated/static refs and perform bounded runtime storage-stat inspection so operators can measure refs missing StorageObject metadata without blocking normal snapshots	this PR
131	AdminAll storage-space reconciliation can persist measured unknown generated/static output refs into canonical StorageObject rows, turning later analyzer runs from unknown DAG refs into known physical-byte accounting	this PR
132	Storage-space analysis now lists duplicate/shared storageId rows with user/content counts, logical bytes, deduplicated physical bytes, catalog refs, and post refs for safer drilldown before cleanup decisions	this PR
133	Storage-space analysis now lists pinned canonical StorageObject rows with physical bytes and DB-visible content, catalog, post, and generated-output reference counts that explain cleanup blockers	this PR
134	Storage-space analysis now reports preview/thumbnail refs by preview field with content row counts, canonical StorageObject preview metadata counts, deduplicated physical preview bytes, and unregistered preview storage IDs	this PR
135	Storage-space analysis now exposes an on-demand bounded content cleanup-blocker drilldown that reuses database.getContentDeleteSafety content/storage blocker keys and counts for operator cleanup screens	this PR
136	Storage-space analysis can now inspect immediate IPFS DAG children for generated/static output refs and reconcile those measured child refs into canonical StorageObject rows through bounded AdminRead/AdminAll routes	this PR
137	Generated-output child reconciliation now stores durable source-to-child StorageObjectReference edges, and delete safety blocks physical child removal while the generated-output parent is still DB-visible	this PR
138	Generated-output child inspection/reconciliation now supports bounded recursive DAG traversal with depth/node limits, and delete safety follows stored child-ref ancestors back to a DB-visible generated-output source	this PR
139	Remote pin attempts now write a model-sync PinStorageObject ledger row per pin account and storage ID; provider acceptance is distinct from confirmed availability, uncertain nonterminal states block deletion, and storage-space counts only confirmed/legacy-confirmed remote pins	this PR
140	Storage-object physical removal now has a storageSpace async queue that rechecks database.getStorageObjectDeleteSafety immediately before unpin/remove; file-catalog content deletion routes safe physical removals through that queue and no longer lets the deleted row's own preview refs self-block cleanup	this PR
141	Content preview CIDs now seed their own canonical StorageObject rows and preview StorageObjectReference edges; restored-data storage-object repair recreates both, and file-catalog content deletion queues distinct original and preview storage IDs for final safety-checked cleanup	this PR
142	Storage-space analysis now exposes deterministic availability/popularity signals from DB-visible refs, local pins, remote pin ledger rows, and stored content/post/group peer counts without requiring live DHT/provider sampling in snapshots	this PR
143	Storage-space analysis now exposes a bounded on-demand availability-network inspection route that samples Kubo/Helia provider lookup and timed storage stat signals without adding live network calls to cached snapshots	this PR
144	Availability-network inspection results can now be persisted as StorageSpaceAvailabilitySample history and shown later without a live DHT lookup on page load	this PR
145	Availability-network sampling now has an opt-in bounded production worker with conservative provider/stat limits plus retention cleanup for old sample rows	this PR
146	Auto-action log error and response payload columns are widened to TEXT so successful actions with JSON checkpoint responses do not fail only while writing diagnostic log rows	this PR
147	Content library deletion now soft-deletes unreferenced Content rows, hides them from normal reads/storage-space reports, and originally attempted an active-row uniqueness constraint for re-upload compatibility; row 153 records why that constraint was reverted	1a4d3b8
148	Admin tombstone routes can list and restore soft-deleted content rows while physical storage still exists and no replacement active same-user row conflicts	this PR
149	Admin tombstone purge routes now preview and hard-delete only expired soft-deleted content rows whose physical storage is already missing and whose content row has no active DB references; upload accounting rows are preserved with their content link detached	this PR
150	StorageObject now has nullable identityType / identityId / identityUrl metadata plus an identity lookup index and sync helper so ownerless/federated objects can be represented without creating Content.userId = null library rows	this PR
151	Remote GeeSome content manifest imports now seed canonical StorageObject metadata and geesome-content-manifest identity before actor-scoped library creation, including ownerless cold-cache attempts that still reject Content.userId = null rows	this PR
152	database.getStorageObjectByIdentity(identityType, identityId) now resolves canonical storage rows through the indexed identity pair for policy-specific callers without changing public serving authorization	this PR
153	File-catalog active path uniqueness remains migration-backed only, so restored production duplicates are cleaned before file_catalog_items_child_path_unique / file_catalog_items_root_path_unique are created instead of letting model sync block startup first	this PR
154	Docker startup and migrate-all-database now fail closed when sync or any migration module fails, instead of continuing into a partially initialized API with missing modules	this PR
155	Pin provider reconciliation now uses bounded durable async-operation jobs, Pinata pinned/job status inspection, expiring per-row claims, PostgreSQL per-account advisory locks, and bounded retry backoff without starting periodic provider traffic yet	this PR
156	Pin provider reconciliation now has an opt-in immediate-start interval worker with bounded config, non-overlapping sweeps, claim-time due checks that suppress stale duplicate queue jobs, and shutdown draining/restart recovery	this PR
153	Content(userId, storageId) uniqueness is reverted because restored production data contains valid same-user duplicate storage rows; the runtime model and migrations now keep (storageId, userId) as a non-unique actor lookup and explicitly drop the failed index	this PR
157	Image compositions now store the versioned recipe on an immutable baked PNG Content, use model-sync ContentDependency edges for the original/SVG graph, and use model-sync root/publication identity plus operation rows for convergence and recoverable idempotency without adding a composition-specific production migration or Post metadata	this PR

The findings/plan tables below call out the remaining risk after shipped slices. When a row references shipped work, its evidence is scoped to the pieces still missing rather than the already-landed behavior. Backlog items (A2 carve-out, post event/revision, etc.) are listed in their own section after the implementation plan.

This review focuses on GeeSome Node running either locally or on a server, with groups that may hold hundreds of thousands of posts and multiple content records per post. The goal is to identify the first database/query changes needed before large imports, ActivityPub federation, social-network mirroring, or long-lived group feeds create very large local datasets.

Current Shape

The current group storage model is straightforward:

• Post rows hold group timeline data, manifest IDs, source import IDs, counters, and status flags. Timeline and manifest lookup indexes now cover (groupId, isDeleted, status, publishedAt, id), (groupId, status, updatedAt, id), (groupId, id), cleanup-backed unique (groupId, localId), cleanup-backed partial unique (groupId, source, sourceChannelId, sourcePostId), and manifestStorageId, alongside the older name/reply/repost/social indexes. Active published create/update paths now allocate localId under a Postgres row lock inside the same transaction that writes the post row, attachments, size, relation-counter repair, and group counters, and the allocator self-heals stale high-water counters from indexed MAX(localId). Create/update/delete paths share reconcilePostRelationCounters for reply/repost count repair. Delete now transaction-scopes the tombstone flag, group size/availability deltas, and affected relation counters, then regenerated group manifest tries remove deleted and newly unpublished local IDs. Ordinary post create/update/delete writes append post_lifecycle events, and source-identity imports additionally append source_import events in the same DB transaction. Restored-backup migration repair now backfills legacy null/mismatched Post.size values from PostsContents -> contents and verifies the final aggregate in the migration integrity audit. Group manifests now dual-write a paged post-index sidecar, persist a durable post-change cursor for compatibility scans, default to omitting legacy inline posts above 1,000 available posts, rewrite only touched postsIndex pages when a previous paged index exists, and remote import can consume chunked-only indexes. Post/group manifest rebuilds now default to the group-derived-state queue backed by UserOperationQueue and UserAsyncOperation; successful jobs record post/group manifest and static-directory checkpoint IDs, retry errors include attempt counts, queue kicks process bounded batches, an interval worker resumes waiting jobs by default, the May 15 posts dump passed guarded async queue rehearsal, GROUP_DERIVED_STATE_ASYNC=0 and internal asyncDerivedState: false options remain synchronous escape hatches, and encrypted personal-chat post manifests still stay immediate. Missing pieces are larger restored-dump reruns when available and richer revision payloads.
• Content rows hold uploaded media/file metadata. Ownership lookup is indexed by non-unique (storageId, userId), actor-scoped manifest import is indexed by (userId, manifestStorageId), manifestStaticStorageId is unique, and global manifestStorageId lookup is also indexed. Production restores showed legacy same-user duplicate (userId, storageId) rows, so the former cleanup-backed uniqueness constraint is reverted and the database must not enforce one row per user/storage object. Content library deletion now marks unreferenced rows with isDeleted/deletedAt instead of hard-destroying them, and normal content reads and storage-space reports filter hidden rows. Admin tombstone routes list deleted rows, restore them before physical cleanup only while storage still exists, and purge expired tombstones only when the physical storage object is already missing and row references are clear. The implementation still tries to dedupe normal uploads through actor-scoped lookup, but callers must tolerate duplicate candidates by using deterministic ordering or explicit content IDs; two different users, and old same-user retries, can own separate Content rows with the same physical storageId. Content.size is already BIGINT.
• Image compositions deliberately create a distinct baked PNG Content entity per immutable recipe revision even when the physical PNG storageId deduplicates. ContentDependency has a unique (parentContentId, role, position) slot and an indexed (childContentId, role, id) reverse path so original/SVG children block deletion while referenced. ImageCompositionIdentity uniquely converges (userId, compositionId) roots and binds each lineage to one stable file-catalog item; revisions CAS-swap that item to the new immutable Content while operation rows preserve retry recovery and delete safety. Posts and groups have no composition-specific binding. Catalog list filtering happens before bounded pagination, though the joined propertiesJson marker predicate should be measured before very large per-user catalogs.
• StorageObject rows are the first canonical physical-storage registry: one model-sync-created row per storageId, with physical MIME/type/size fields, preview metadata refs, the local isPinned marker, and nullable ownerless/federated identity metadata (identityType, identityId, identityUrl, identityUpdatedAt). New content writes and preview metadata updates seed or refresh the original row, each distinct preview CID seeds its own physical StorageObject row, remote GeeSome content manifest imports seed geesome-content-manifest identity from the parsed manifest before any actor-scoped library row is created, database callers can resolve canonical rows by indexed identity pair, shared public storage/header metadata reads prefer exact StorageObject rows with deterministic Content fallback for old rows, successful Pinata pins mark both StorageObject.isPinned and legacy Content.isPinned, restored/upgraded databases can reconcile missing or stale canonical rows and preview edges from Content metadata plus any legacy pinned row, and AdminAll storage-space reconciliation can persist measured generated/static output refs that do not yet have StorageObject rows. StorageObjectReference now keeps model-sync preview parent -> child edges and generated-output parent -> child edges across bounded recursive DAG walks so child cleanup can see references that are not represented by a user Content row. Authorization, per-user naming, quota, manifests, and library behavior still live on Content; future ActivityPub/generated producers and product-level public lookup policy should write canonical object identity onto StorageObject instead of creating ownerless library rows.
• FileCatalogItem rows now have the reverse (contentId) lookup for content deletion/reference checks, (userId, parentItemId, isDeleted, type, createdAt, id) for large folder pages, and cleanup-backed active path uniqueness split between child rows and root rows because Postgres unique indexes allow multiple NULL parents. Exact path saves now look up by exact name rather than LIKE, duplicate-key races retry through the existing active item, generic folder additions suffix duplicate upload names instead of failing raw uniqueness, and user-facing folder browsing caps list windows while allowing only known sort fields. Internal folder publish traverses child folders/files in bounded id-ordered batches, so it does not inherit the public browsing cap or the generic default page.
• PostsContents connects posts to content records and stores the content order (position) and per-post view inside a post. The through table primary key already prevents duplicate (contentId, postId) rows; the model now also has cleanup-backed unique (postId, position) enforcement plus a (contentId) reverse index, and manifests read per-post attachment view from the join row.
• Group keeps publishedPostsCount, availablePostsCount, size, and the nullable manifestPostsCursorUpdatedAt / manifestPostsCursorId pair used as the durable group-manifest post scan cursor. availablePostsCount and size are maintained incrementally during post create/update/delete, while publishedPostsCount acts as the group-local post ID high-water mark rather than a live published count. Local IDs are unique and stable, not gapless; deletes, unpublishes, duplicate repair, and derived-state failures may leave gaps, and the allocator now advances from the greater of publishedPostsCount and indexed MAX(localId). Generated static-site pages display availablePostsCount as the public post total and only fall back to the high-water field for older/incomplete rows. Create/update/delete counter effects are transaction-scoped with the canonical post DB write, and the restored-backup backfill repairs Group.size / availablePostsCount from active published posts after repairing post sizes. updateGroupManifest no longer recomputes full SUM/COUNT scans on every regeneration; reconcileGroupCounters remains available for repair. The model declares explicit indexes for manifestStorageId, manifestStaticStorageId, creator-owned group listings (creatorId,type,isDeleted,createdAt,id), and the static-rebind scan (isDeleted, staticStorageUpdatedAt), plus the local (name, isRemote) uniqueness scoped to isRemote: false, isCollateral: false. The disabled static-rebind helper now returns a capped, deterministic oldest-first candidate batch so manual/ops use does not accidentally scan every outdated group at once.
• GroupRead stores a per-user read cursor (userId,groupId unique). New rows can carry readPostId alongside readAt, so unread counts can follow timeline ordering (publishedAt DESC, id DESC) and still fall back to timestamp-only semantics for old rows/clients. The group timeline index backs the count; remaining work is client adoption and any future migration of old read rows if exact historic boundaries are required.
• groupMembers, groupAdministrators, and groupPermissions through tables now have explicit user/group lookup indexes for permission/admin checks.
• StaticIdHistory declares unique (staticId, dynamicId), (staticId, boundAt), a single-column (staticId) index, and a (dynamicId, boundAt) lookup index. StaticIdBinding now keeps one current row per staticId, with unique staticId and (dynamicId, boundAt) indexes, so hot resolution does not have to scan churn-heavy history. The table is created by Sequelize model sync rather than a creation migration; upgraded databases lazily fill current rows from history on first static/dynamic lookup. New history churn is compacted per static ID after binding: the cleanup keeps the newest STATIC_ID_HISTORY_RETAINED_ROWS rows, preserves any row matching StaticIdBinding, and deletes only up to STATIC_ID_HISTORY_CLEANUP_BATCH_LIMIT stale rows at a time. Whole-table startup cleanup is off by default and can be enabled with STATIC_ID_HISTORY_STARTUP_CLEANUP_BATCH_LIMIT after a backup if an existing node wants to drain old history gradually.
• UserContentAction is the upload/pin accounting ledger used by quota checks. It now has (userId, name, createdAt) and (contentId) indexes and keeps size as BIGINT; UserLimit has cleanup-backed unique (userId, name) enforcement and the setter retries through the update path on duplicate-key races. Upload streams still do a pre-stream remaining-size check, then the visible Content row and upload action commit together inside a transaction that locks the active UserLimit row and re-checks upload/pin totals. This prevents two concurrent uploads from both committing past the same limit, but bytes may still have reached storage before the final commit rejection; true pre-stream reservation/release remains a later durable-upload-job shape.
• Tags, mentions, and auto-tag models exist and now have explicit lookup indexes for tag names/manifests, post-tag pivots, mention source/target refs, and auto-tag group/tag refs. They remain quiet today; before promoting them as feed filters or ActivityPub/social graph features, add endpoint-specific cursor/count policy and large-fixture EXPLAIN coverage.
• API group timelines and global post listings now select the requested page of Post IDs first and hydrate contents/repost data only for that bounded ID set. Group post feeds support cursor pages that skip total, and offset callers can pass includeTotal=false to keep the legacy list shape without paying a count query. Global post listings also expose cursor-aware lightweight refs and a batch walker for export-style scans before bounded hydration. User member/admin/personal-chat group lists now default/cap pages, restrict sort fields, use stable id tie-breakers, and use count-only totals instead of returning unbounded association rows; the creator/type/deleted personal-chat path has a matching list index. User friend lists now default/cap pages, restrict sort fields, apply deterministic ordering, and make search filters consistent between list and total. Entity group-manifest generation now uses lightweight post-ref scans in (updatedAt,id) cursor batches instead of full content/repost hydration or fixed large windows, dual-writes a paged post-index sidecar, and can omit the legacy inline trie for chunked-only manifests through an explicit option or count limit. Social-import reversal now uses lightweight post refs in (publishedAt,id) cursor batches instead of hydrated timeline rows or one fixed large window. Static-site generation now scans post refs in cursor batches, hydrates each bounded batch, streams SSR through current page/post state instead of one final posts[], and renders the live available post count instead of the local-ID high-water mark. RSS defaults to a smaller feed window, keeps explicit limit requests capped at the legacy 9999-item maximum, and still scans/hydrates ref batches. The fixture now has mixed image/text/json metadata, and database:scalability:generated-output reports whether remaining generated-output pressure comes from unique body reads, repeated body reads, non-text copy candidates, or root group-manifest inline ref pressure; the static-site management list now defaults/caps pages and uses count for total instead of loading matching rows.
• Category feeds use GroupCategory/pivot tables and now follow the same ID-first page hydration shape as group timelines. The page selector keeps category/group pivots for filtering but projects only post id, the ordering column, and the required group id alias before hydrating the bounded page. Category posts support (publishedAt,id) cursor pages and includeTotal=false offset pages that skip total, while category group/section management lists have known sort fields and lower browsing caps. Category and section aggregate size fields are now BIGINT, matching the post/group/content/quota byte counters instead of keeping a quieter 32-bit ceiling. Remaining category-feed work is legacy offset migration/caching policy and large-export behavior.
• An async-operation layer already exists: UserAsyncOperation is filtered by (userId, inProcess, name) and ordered by createdAt; on every node start closeAllAsyncOperation marks in-flight rows as stopped after restart, then cleanupFinishedAsyncOperations deletes old finished rows plus linked/closed orphan queue rows in bounded batches. The ledger now has (userId, inProcess, name, createdAt) and (inProcess, updatedAt) indexes, and output is TEXT so derived-state jobs can store JSON checkpoint payloads. UserOperationQueue has (module, isWaiting), (module, inputHash, isWaiting), (asyncOperationId), and (isWaiting, asyncOperationId, updatedAt, id) indexes, and the user-visible waiting queue list now defaults/caps pages with a stable sort allowlist. Group post/group manifest derived-state jobs now default through this queue with input-hash dedupe, explicit async-operation rows, bounded retries, success checkpoint output, attempt-aware failure messages, bounded per-write kicks, an interval worker, and GROUP_DERIVED_STATE_ASYNC=0 as the operator escape hatch. Storage-object physical removal also uses the queue with a configurable retention delay, optional worker, and bounded AdminRead history view. Availability-network sample refreshes can use the same queue, and an opt-in worker can periodically enqueue a conservative top-page sample while retention cleanup bounds persisted sample history. A guarded restored-data async rehearsal command enqueues missing manifest/static repairs, drains bounded batches, and reruns integrity; the May 15 posts dump passed this flow with final integrity at zero issues. Remaining work is cursor semantics if users need long operation history beyond capped operator views, and larger restored-dump reruns when available.
• AutoAction is the scheduled-task store. Its executor query now uses the indexed isActive=true AND executeOn <= now predicate with deterministic (executeOn, id) ordering and a bounded due-action batch. Cron workers claim due rows with FOR UPDATE SKIP LOCKED, store executeClaimedAt / executeClaimExpiresAt, and skip rows already claimed by another process until the claim expires. Fresh model sync creates the claim columns/index on empty databases, while upgraded databases get the low-lock concurrent index from the migration. If new code starts against an unmigrated old table, the model omits the claim columns and falls back to the previous bounded read path until the migration and restart enable DB claims, avoiding startup crashes during rollout. The user-facing management list now separates filter fields from list params, defaults/caps pages, returns count-only totals, and has a (userId, createdAt, id) index for the default page order. Auto-action log error and response columns are TEXT so JSON diagnostics/checkpoint output cannot turn a successful action into a log-write failure. The in-process cron queue still tracks action IDs already queued or running so a long-running action is not requeued inside one node before DB state is updated. Remaining scheduler work is product-level TTL/idempotency policy for very long external jobs.
• PinAccount indexes cover (service, userId), (service, groupId), cleanup-backed unique (userId, name), and cleanup-backed unique (groupId, name). Existing duplicate owner/name rows are preserved by renaming the non-winning rows before the unique indexes are created, so runtime owner/name lookups are deterministic after rollout. Public user/group pin-account lists reuse shared list-param sanitization, allowlist sort fields, default pages, and cap responses at 100 rows. Direct-user lookups now require groupId IS NULL; group-scoped credentials are controlled by current group editors, while their userId remains creator/audit and auto-action ownership metadata without granting permanent access. Account scope is immutable, deletion retains ledger history without remote unpin, and automatic discovery uses complete stable (name, id) cursor batches rather than silently stopping at 100 accounts. Group post automatic pinning constructs selected manifest/content targets in memory and bulk-loads existing (pinAccountId, storageId) ledger pairs before enqueueing jobs.
• ForeignAccount rows have indexes for user lists, user/provider lookup, provider/address lookup, and auth-message account lookup. The user-facing account list now uses shared list-param sanitization, allowlisted sort fields, default pages, and a 100-row cap; the manifest hook still calls the internal no-list-param path so user manifests include all linked accounts instead of only the first page.
• SocNetAccount rows have user/social-network lookup indexes for phone-number and username matching. The user-facing list endpoint now reuses shared list-param sanitization, allowlisted sort fields, default pages, and a 100-row cap while continuing to accept the legacy accountData.socNet filter shape.
• ContentBots list rows for Telegram content-bot setup. The current-user list endpoint now reuses shared list-param sanitization, allowlisted sort fields, default pages, and a 100-row cap, and fresh/upgraded schemas carry a content_bots_user_id_idx lookup index for the user-scoped list.
• database/models/object.ts backs getDataStructure/saveDataStructure as a data-structure cache. The cache key is now (storageId, resolveProp) with non-null resolveProp, so resolved path reads and unresolved/raw reads for the same storage path do not collide or block each other. It remains a small cache table rather than the future A2 canonical storage-object table.
• database/config.ts selects Postgres and now sets an explicit, environment-overridable Sequelize pool. The wrapping class is named PostgresDatabase.
• createPost, active published transitions in updatePost, and remote post manifest imports now use sequelize.transaction plus FOR UPDATE on the group row so local ID allocation, the post row, PostsContents, size, reply count, group counters, and post event rows commit or roll back together. deletePosts now wraps the tombstone flag, group counter deltas, and affected reply/repost counter repair in one DB transaction, and regenerated group manifests remove deleted and newly unpublished local IDs. Remote group import now compares the remote manifest trie to active local refs, tombstones stale removed/replaced refs, clears reused remote local IDs so replacements can land, and repairs group counters. Manifest/static generation, richer remote edit/delete history, and the broader write paths in app/modules/content, app/modules/database, and app/index.ts still mostly run as sequences of independent statements.

That design is workable for small and medium groups, but the hot paths need stronger indexes and page-oriented loading before 100k+ posts per group becomes normal.

General Schema Assessment

The current scheme has the right core components for the GeeSome model:

• Group represents the feed/chat/channel container and carries both DB ownership state and distributed manifest/static IDs.
• Post represents timeline entries, imported social messages, replies/reposts, encrypted post references, and distributed manifest references.
• Content represents uploaded or remote media/file metadata plus previews and manifest references.
• PostsContents connects posts to content records and stores attachment order and per-post content view.
• GroupRead, group members/admins, and group permissions provide the current access/read-state base.
• JSON manifests let groups, posts, users, and content be addressed by storage IDs and static IDs outside one node database.

So the design is flexible enough for the MVP and for local/server GeeSome nodes. The missing part is not a totally different schema; it is stronger lifecycle boundaries. DB rows, storage objects, copied post directories, static IDs, and group manifests are currently updated inside the same synchronous application flow. That is convenient for small data, but fragile for large groups, federation, retries, and offline clients.

Storage And Manifest Flow

Current content flow:

1. Upload or import saves a storage object and preview objects.
2. Content metadata is created with storageId, preview storage IDs, size, MIME/type/view, optional propertiesJson, and later manifestStorageId.
3. Normal upload dedupe checks (storageId, userId), but the database no longer enforces unique (userId, storageId) because production restores contain legacy same-user duplicates. This allows separate user-owned metadata rows for the same physical storage object and requires callers to keep deterministic duplicate handling.
4. A content manifest stores the content metadata plus preview references.

Current post-to-group flow:

1. createPost checks group permission and reply permission.
2. Attachment refs are resolved through the acting user's content row: DB IDs require owner/public, manifest IDs import or claim an actor-scoped row, and storage IDs are actor-scoped.
3. If the post is published, the canonical DB transaction locks the Group row, allocates localId, writes the post row, updates PostsContents, stores post size, updates repliesCount, and increments group counters.
4. The post stores author/group manifest IDs, social source IDs, status/type/view fields, and optional encryption metadata.
5. A post manifest is generated after the DB transaction commits.
6. Post contents are copied into a group static directory and a directory manifest is written.
7. The group manifest is regenerated and rebound to the group's static ID.

Content identity is the piece most likely to be misread. The physical storage object is globally addressed by storageId, while the Content row is a user-owned library entry. That split is acceptable, but only if every caller is clear about which identity it wants. User content APIs such as content-by-storage-id are user-scoped, and post attachment by DB ID/manifest ID now resolves through the acting user's content row with an explicit reason (owner, public, actor manifest import, or actor storage). Remote group avatar/cover imports and remote user avatar imports resolve media through the importing actor when one exists. Legacy global manifest lookup methods still delegate to deterministic shared helpers that pick the oldest row by id, which makes metadata reads stable but does not make them owner-authorized. Ownerless remote content import is read-only against existing shared rows, seeds geesome-content-manifest identity on the canonical StorageObject for a cold-cache manifest, and still fails with content_actor_required before creating a new user-library row. Public content/:contentId metadata now returns a safe projection, hides private rows looked up by numeric DB id, and storage-id/header lookups prefer StorageObject while falling back to deterministic Content rows for old data. New content writes seed a model-sync StorageObject row keyed by storageId, record physical metadata, create/update canonical preview rows and preview edges, and carry canonical local pin state without changing owner/library policy. Remote pin attempts write a model-sync per-account PinStorageObject state row, including manifest targets with no Content row, so provider acceptance, confirmation, uncertainty, and failure are independent from the local pin boolean. Restored-data repair can backfill the same canonical original rows, preview rows, and preview edges from existing Content data before integrity checks. Remaining sensitive areas are callers that treat shared rows as permission-sensitive and future share/group/admin attachment reasons.

Chosen model (A1): keep per-user Content rows and use (storageId, userId) as a non-unique actor lookup, with post attachment resolving content through the acting user's row or an explicit share/claim path. Public file/preview serving uses storage-layer stats plus StorageObject metadata when present, and local pin state now records on the canonical storage row while legacy Content.isPinned remains for compatibility. Funnel every "shared" read (public serving headers, preview metadata, pin reference state) through a single helper today so later canonical-row behavior stays mechanical. Do not re-add Content(userId, storageId) uniqueness without a production duplicate policy and a restored-backup rehearsal.

First A2 on-ramp: StorageObject is now a model-sync-created registry keyed by storageId, populated from new Content writes and refreshed when content preview/physical metadata changes. Remote GeeSome content manifest imports also seed canonical physical metadata and geesome-content-manifest identity from the parsed manifest before actor-scoped library creation, so an ownerless cold-cache import can be represented as a physical object while still rejecting Content.userId = null. Database callers can now resolve the canonical row by (identityType, identityId) for policy-specific workflows; shared public storage/header metadata still resolves by storage ID and falls back to deterministic Content rows, so old upgraded databases keep serving before repair. npm run database:storage-objects-integrity -- --repair reconciles restored/upgraded original rows from the oldest Content metadata per storageId, recreates preview rows and preview reference edges, and carries forward any legacy Content.isPinned marker. Remote attempts record a separate pin-account/storage state row; only confirmed claims count in remote-pin storage analysis, while requested, accepted, retryable, confirmed, and legacy-confirmed claims block physical deletion. Provider acceptance no longer mutates the canonical local pin bit. This does not make Content.storageId globally unique and does not change authorization.

Remaining A2 destination: keep physical refcount/reconciliation state and ownerless/federated object identity on StorageObject or a later contentAssets shape while keeping Content as the per-user ownership/library row. The first identity metadata pair is present on StorageObject, GeeSome content manifests now seed it, and a database-level identity lookup helper exists; remaining work is wiring ActivityPub/generated producers, trust policy, and public UI/API semantics to it. This is the long-term shape needed for ActivityPub object IDs that exist independent of any user, for native cross-user pin reference counting, and for clean federation.

Do not add a global unique constraint on Content.storageId under A1. Different users uploading the same bytes is a legitimate case; the bug is not duplicate physical storage, it is resolving a user-owned row through global findOne. A global storageId uniqueness only makes sense after the A2 carve-out, on the contentAssets table.

Same StorageId Across Users

Observed current cases:

Case	Current Behavior	Required Behavior
Same user uploads the same bytes twice	saveData/saveDataByUrl try to return an existing row through (storageId, userId), but legacy production data may already contain multiple same-user rows for one storage ID.	Keep same-user dedupe in the content helper, keep duplicate reads deterministic, and do not enforce a unique DB constraint until a product/data repair policy is proven on restored production backups.
Different users upload the same bytes	A second row is created with the same physical storageId; this is useful for per-user names, folders, quotas, and actions.	Keep this allowed. Resolve all user-facing writes through the acting user's row, not by global storageId.
Different users upload the same bytes and equivalent metadata	The generated manifestStorageId can also be identical, and there is no uniqueness or owner scope. Actor-owned flows use (userId, manifestStorageId); shared/global reads use deterministic id ASC tie-breaking but can still surface another user's metadata row.	If manifests describe user-library entries, keep using (userId, manifestStorageId) lookup. If manifests describe canonical assets, move user metadata out of the manifest-backed row.
User creates a post with another user's Content.id	resolveContentForPost reloads DB IDs and now accepts only the actor's row or a public row, returning owner or public as the permission reason; another private user's row fails with content_not_permitted.	Keep this check centralized and add future share/group/admin reasons here instead of re-opening global DB-ID attachment.
User creates a post with a content manifest ID	resolveContentForPost now calls createContentByRemoteStorageId(actorUserId, manifestStorageId), which prefers (userId, manifestStorageId) and imports an actor-owned row from the manifest when needed.	Keep manifest attachment actor-scoped; decide whether an already-owned same-storageId row should be enriched with the imported manifest metadata or whether a separate ownership/share row is required.
Remote group/user import references avatar/cover content	createGroupByObject and createUserByObject now import avatar/cover/avatar content through the importing actor when one is present and tolerate missing media refs. This prevents imported entities from linking another user's private Content row when manifest IDs or physical storageIds overlap.	Keep group/user media imports on actor-scoped helpers. Extend the same owner propagation to any future profile/media import surface rather than using global shared reads for permission-sensitive links.
Remote post import reconstructs content	Import paths that carry options.userId now create/reuse actor-scoped content rows, and the cold-cache path passes the parsed content object into createContentByObject. The userId = null path can reuse existing deterministic shared manifest/storage rows but cannot create a new ownerless library row.	Keep carrying the importing actor/group owner through remote content creation. Formalize true ownerless remote objects as canonical asset rows in the A2 carve-out rather than reviving userId = null library rows.
Public file/content serving looks up metadata by storage ID or preview storage ID	getFileStreamForApiRequest, getContentHead, and public storage-id metadata call getSharedStorageMetadataByStorageId(..., {includePreviews: true}), which prefers StorageObject and falls back to deterministic Content rows. Storage-layer stats still provide bytes/ranges.	Keep authorization/storage access unchanged while old rows fall back to Content. Add backfill/integrity coverage before removing fallback assumptions.
Public content metadata route receives a DB id or storage id	Numeric DB ids now return only public rows, and all public responses use a projection that excludes userId, propertiesJson, encrypted manifest refs, pin/accounting flags, and other owner-library fields. Storage-id lookups now prefer canonical storage metadata for known content because byte/head serving is public by storage id.	Keep the public route metadata-only. If callers need owner/library data, use authorized user/admin routes. Keep canonical storage metadata free of owner-library fields.

Until the ownership decision is made, global content lookups should be treated as unsafe for permission-sensitive behavior and for any mutation that attaches, shares, accounts, deletes, pins, or imports content.

Current group manifest flow:

1. The group manifest stores group metadata, avatar/cover references, public key/static ID, and post count.
2. For compatibility manifests that still emit inline posts, if a previous group manifest exists, the generator loads its full posts trie and iterates three lightweight post-ref slices from the durable group cursor timestamp when present, falling back to the previous manifest timestamp for older rows until the cursor is established: published non-deleted rows, published deleted rows, and non-published rows. The scan intentionally overlaps the stored timestamp bucket and uses (updatedAt,id) only for in-scan batch progress, so a same-timestamp update on a lower-id post is rechecked instead of skipped. Note: this filter keys off Post.updatedAt, not publishedAt, so the published-timeline index does not help this path.
3. Changed non-deleted posts are written into the trie by localId, pointing to either the post manifest reference or encrypted post manifest reference. Changed deleted/unpublished posts unset the inherited local ID from the trie.
4. Group counters are maintained incrementally during post writes; updateGroupManifest no longer runs per-regeneration SUM/COUNT scans, while reconcileGroupCounters can still repair from full scans when explicitly requested.
5. A paged postsIndex sidecar is written from current post refs. When a previous postsIndex exists, regeneration uses the durable (updatedAt,id) cursor to rewrite only touched pages. The generator omits the legacy inline posts trie when the group exceeds the 1,000-post default cutoff, when GROUP_MANIFEST_INLINE_POSTS_LIMIT / inlinePostsLimit is exceeded, or when includeInlinePosts: false is passed.
6. The final group manifest is stored and bound to the group static ID.

This is conceptually strong: group manifests can become append/update/remove indexes over post manifests, and post manifests reference content manifests. Changed-ref batching, a durable post-change cursor, status-removal tombstones, remote group import edit/delete replay, chunked post-index compatibility, default chunked-only generation above 1,000 posts, page-level post-index rewrites, a manual derived-state integrity/repair command for local published post/group manifests, a default-on durable group-derived-state queue with checkpoint output and an interval worker, and a guarded restored-data async rehearsal command are now in place. The May 15 posts dump passed the async queue repair flow; the implementation still needs larger restored-dump reruns when available and broader reconciliation repair paths before it can be trusted under heavier write/load conditions. The IPFS/IPLD storage direction is captured in docs/group-manifest-ipld-scalability.md: keep ordered postsIndex pages for the group timeline now, reserve HAMT/ADL maps for unordered canonical asset indexes later, and consider CARv2 only for export/backup bundles rather than the live group manifest.

Findings

Priority	Finding	Current Evidence	Recommended Direction
P1	Post writes are only partly atomic because derived manifest/static publishing still happens after the canonical DB transaction.	createPost, the publish transition in updatePost, and the canonical DB portion of deletePosts now wrap their DB state in one transaction: local ID allocation, post row, PostsContents, size, reply count, group counters, tombstone flags, and affected reply/repost counters. Group manifest regeneration now unsets deleted and newly unpublished local IDs, and remote group replay tombstones stale manifest refs before importing replacements. After commit, database:derived-state-integrity catches active published posts/groups whose DB row survived but local manifest/static derived columns did not, and the repair path can regenerate local post manifest/directory state through updatePostManifest plus missing group manifests through updateGroupManifest. The default group-derived-state queue now covers post/group manifest rebuilds with input-hash dedupe, bounded attempts, async-operation status rows, manifest/static-directory checkpoint output, attempt-aware retry errors, bounded per-write kicks, an interval worker (GROUP_DERIVED_STATE_WORKER, defaulting on with GROUP_DERIVED_STATE_ASYNC), a restored-data rehearsal command that passed the May 15 posts dump, and synchronous escape hatches via GROUP_DERIVED_STATE_ASYNC=0 or internal asyncDerivedState: false options.	Keep the canonical DB transaction boundary. Keep encrypted personal-chat post manifests synchronous, rerun database:derived-state-async-rehearsal on larger restored production-shaped data when available, and keep the integrity/repair command as the manual safety net.
P2	Group-local post numbering is unique/stable, but intentionally not gapless.	Local ID allocation locks the group row, is folded into the create/update post transaction, advances from the greater of publishedPostsCount and indexed MAX(localId), and is backed by cleanup-backed unique (groupId, localId). The migration preserves duplicate legacy rows by reassigning later duplicates above the current group high-water mark.	Treat localId as a stable manifest/static identity, not a dense sequence. Keep repair/reconciliation paths for manifests and counters after derived-state failures, and do not reuse local IDs after delete/unpublish/backfill gaps.
P1	Manifest/static publishing is now queued by default, but static delivery status still needs richer job-level state.	updatePostManifest writes the post manifest, copies post content into the group static directory, updates the group directory, then calls updateGroupManifest. A repair wrapper now reuses this path for local published posts that are missing manifestStorageId or directoryStorageId, and it can call updateGroupManifest directly for groups with active published posts but no manifest. The read-only audit reports missing local IDs, public manifests, local static directories, encrypted manifest refs, and group manifests. queuePostManifestUpdate, queueGroupManifestUpdate, and processDerivedStateQueue now provide the default durable derived-state queue path, completed jobs expose post/group manifest plus static-directory checkpoint status in async-operation output, per-write kicks process bounded batches, the interval worker can resume waiting jobs after restart, and operators can temporarily restore synchronous behavior with GROUP_DERIVED_STATE_ASYNC=0.	Treat manifest/static generation as derived state. Store post first, let async operations rebuild post/group manifests idempotently, and rerun the guarded async rehearsal command plus Docker coverage when larger restored dumps appear. Add richer static delivery state only when product-visible delivery semantics need it.
P2	Core timeline reads now avoid content joins during page selection, but large callers still need product-specific export policy.	getGroupPosts keeps keyset/cursor mode and now scans only Post.id/publishedAt for the requested page before hydrating contents/repost data for those IDs. Cursor pages skip total, and offset callers can pass includeTotal=false to skip the count query while preserving the list response shape. getAllPosts uses the same ID-first page scan, and getAllPostRefs / forEachAllPostRefBatch let all-post callers iterate lightweight refs by cursor before hydrating bounded windows. Remaining risk is callers that intentionally request very large hydrated windows or exact counts.	Keep the ID-first loader for API timelines, prefer cursor or includeTotal=false for large browse flows, and route export jobs through cursor/batch projections before hydration. Cache or explicitly request totals only where the UI truly needs them.
P1	Delete/unpublish now remove manifest trie nodes, but derived-state events remain.	deletePosts wraps the tombstone flag, group size/availability counter deltas, and affected reply/repost counter repair in one DB transaction, then regenerates the group manifest after commit. Published -> draft/unpublished transitions decrement group counters and regenerate the group manifest. updatePost now treats isDeleted as leaving the active published lifecycle, so source-identity import tombstones decrement group/relation counters and remove the local ID from the inherited manifest trie. Canonical create/update/delete writes append post_lifecycle events, and source-identity imports append source_import events inside the same DB transaction. Group manifests also write a paged postsIndex sidecar; generation omits inline posts by default above 1,000 available posts while keeping explicit compatibility overrides, and remote import can read chunked-only manifests.	Extend the event layer to manifest/static jobs, delivery state, and richer revision payloads so derived work can be replayed, audited, and reconciled beyond mutable row state.
P1	Compatibility group manifest updates still keep a legacy monolithic post index in memory for small or explicitly forced inline manifests.	When inline posts remain enabled, generateGroupManifest loads the previous group manifest object via app.ms.storage.getObject, copies its full posts trie (a nested JS object built by geesome-libs/src/base36Trie.ts over trie.ts's in-memory upserts), mutates it from cursor-batched changed/deleted/unpublished lightweight ref slices, and serializes the compatibility posts trie plus a paged postsIndex sidecar. The scan now persists Group.manifestPostsCursorUpdatedAt / manifestPostsCursorId, resumes from the stored timestamp with a same-timestamp overlap, and falls back to the prior manifest updatedAt only until upgraded rows get a cursor. Chunked-only generation is the default above 1,000 available posts, avoids loading/copying the inline trie, and when a previous postsIndex exists it rewrites only pages touched by changed/deleted/unpublished local IDs.	Keep the durable cursor as the state primitive for the later async/page-update worker and reserve full recompaction for explicit repair, compatibility overrides, or protocol upgrades.
P2	Remote content canonical ownership is still under-defined for true canonical assets.	createContentByRemoteStorageId now passes the parsed content object and is actor-scoped when an importer user is supplied. When userId is null/undefined, it only reuses deterministic shared manifestStorageId or shared storageId rows, seeds canonical StorageObject metadata/geesome-content-manifest identity for cold-cache manifests, and rejects new ownerless library-row creation with content_actor_required. StorageObject now has nullable identity metadata, an indexed identityType/identityId helper for the canonical-asset path, and a database lookup helper for policy-specific identity resolution.	Keep actor/group owner propagation mandatory for post/group/user media imports. If a non-GeeSome remote object truly needs no owner, write explicit canonical identity onto StorageObject or a later asset table rather than storing more userId = null library rows.
P2	Attachment authorization resolver has the A1 reason shape, but future share/admin reasons still need policy.	resolveContentForPost now rejects another private user's DB ID, resolves manifest IDs through actor-scoped import/claim, and returns explicit reasons for owner rows, public rows, actor manifest imports, and actor storage lookups. There is still no group-shared content row, admin import claim reason, or persisted audit reason on the attachment.	Keep all create/update/import attachment paths on this resolver and add new explicit reasons before adding group libraries, federation trust rules, or admin override semantics.
P2	Content garbage collection has a safe first retention and history path, but broader cleanup policy remains product work.	fileCatalog.deleteFileCatalogItem(..., {deleteContent}) now destroys the catalog item first, asks database.getContentDeleteSafety for the shared content/storage reference-count decision while excluding the catalog row being deleted from derived file-catalog storage refs, preserves the Content row while posts/file catalog/avatar/cover refs or legacy row pins remain, soft-deletes otherwise unreferenced Content rows with isDeleted/deletedAt, records every remote Pinata attempt in the per-account PinStorageObject state machine, and queues physical storage removal only when same-storage, preview, all known DB-visible derived storage columns, current static-ID refs, canonical local pin refs, protected remote-pin refs, and durable storage-object child refs are clear. Requested, accepted, retryable-failure, confirmed, and legacy-confirmed remote states fail closed; terminal-failure and confirmed-missing states do not block forever. File-catalog content deletion now queues each distinct original/large/medium/small preview storage ID, and the storageSpace removal queue rechecks database.getStorageObjectDeleteSafety immediately before unpin/remove, so races, late refs, shared preview refs, and generated-output child refs block removal with the same storage blocker counts. STORAGE_SPACE_STORAGE_REMOVAL_DELAY_MS can hold queued removals for operator review, STORAGE_SPACE_STORAGE_REMOVAL_WORKER* can drain ready removals in bounded batches, and GET /v1/admin/storage-space/storage-removals lists delayed/waiting/blocked/removed/missing/failed history from the async-operation ledger. AdminRead/AdminAll content tombstone routes list and restore deleted rows while physical storage still exists, preview purge eligibility after CONTENT_TOMBSTONE_RETENTION_DAYS (default 30), and hard-delete only expired tombstones after storage is already missing and content-row references are clear. Static-ID history only blocks deletion for the latest row of a static ID that has no current binding yet, so stale compactable history does not pin old objects forever. Storage-space AdminAll reconciliation can now turn measured top-level generated/static output refs plus bounded recursive generated-output child refs into canonical StorageObject rows and durable child edges, and restored-data storage-object repair can recreate canonical preview rows/edges from existing Content. A restored-database storage-space report command now captures active-content analyzer totals/top rows after migration rehearsal.	Keep DB reference checks before content/physical deletion, run restored storage-space reports before cleanup policy changes, and keep future automatic cleanup jobs bounded and operator-visible.
P2	Duplicate same-storageId content rows make remaining shared lookups policy-sensitive.	saveData and saveDataByUrl dedupe with getContentByStorageAndUserId, but production restores can contain same-user duplicates and cross-user same-storage rows. Post attachment resolves by actor, remote group/user avatar/cover imports are actor-scoped when an actor exists, ownerless import cannot create new library rows, public GET/HEAD/ranged file metadata goes through deterministic shared reads, and public content/:contentId returns only projected metadata while hiding private numeric DB ids. Legacy database.getContentByStorageId and database.getContentByManifestId now delegate to explicit shared helpers with id ASC tie-breaking. Remaining policy-sensitive paths are any caller that treats a shared row as owner-authorized. manifestStorageId and (userId, manifestStorageId) are indexed, but the ownership semantics of each shared caller still need to be documented.	Keep actor/user-scoped helpers for writes and authorization; reserve shared storage/manifest lookup for metadata-insensitive public serving with deterministic tie-breaking. Do not make storageId globally unique or (userId, storageId) unique while duplicate library rows can exist.
P2	Preview/header serving still has a broad OR fallback for old rows.	Preview storage indexes exist, and new preview updates now seed exact StorageObject rows for preview CIDs plus preview child refs from the original storage ID. getSharedStorageMetadataByStorageId(..., {includePreviews: true}) prefers an exact canonical row, so new preview HEAD/GET/ranged metadata can avoid the user-library preview OR scan. The helper still falls back to {storageId OR largePreviewStorageId OR mediumPreviewStorageId OR smallPreviewStorageId} across old Content/StorageObject metadata until restored-data repair has reconciled the node.	Keep the deterministic fallback for compatibility, run storage-object repair during restored-backup rehearsals, and only remove the OR fallback after old content preview rows are covered by canonical preview objects.
P2	Content action/accounting can double-count shared physical bytes.	addContent records an upload action for every new Content row, even when another user already has the same storageId. That may be desired for per-user quotas, but it is not the same as physical storage growth.	Track logical user library size separately from physical storage/pinning size. If large imports reuse existing storage IDs, counters and billing/limits should say which dimension they measure.
P2	Content manifest/import dedupe still needs a canonical asset decision.	Actor-owned import now checks (userId, manifestStorageId) separately from (userId, storageId), then falls back to the actor's same-storage row so one actor still gets one library row per physical object. The null/global path now reuses getSharedContentByManifestId/shared storageId, refuses to create a new ownerless library row, and writes the GeeSome manifest identity to canonical StorageObject metadata for cold-cache manifests. Canonical storage objects can carry an indexed external identity pair, but import lookup/product policy still stays explicit rather than treating it as an owner-authorized Content row.	Split lookup helpers by identity and policy: physical storage ID, content manifest ID, static manifest ID, actor-owned content row, and canonical asset. Keep userId = null out of library-row creation; when non-GeeSome canonical assets are needed, route them through StorageObject identity or a later asset table.
P2	Size semantics can still mislead even after BIGINT migration.	Post.size, Group.size, GroupCategory.size, GroupSection.size, UserContentAction.size, and content preview sizes are now BIGINT, but reused content can inflate logical post/group/category/user-library bytes relative to physical stored bytes.	Document whether each counter measures logical attachment bytes, user library bytes, or physical storage bytes before using these fields for billing, quota, or storage-health dashboards.
P2	Unread state now has a post-id tie-breaker, but old read rows may remain timestamp-only.	GroupRead now has nullable readPostId, and getGroupUnreadPostsData counts rows newer than (readAt, readPostId) when present while preserving the old publishedAtGt fallback. A focused test covers equal-publishedAt posts. Existing rows and clients that only send readAt still have timestamp-only behavior.	Have clients send the latest read post id with readAt. If exact historic read boundaries matter, backfill old rows from the latest visible post at the stored timestamp or accept the compatibility fallback.
P2	Social import post identity is DB-backed, and lifecycle events now have a first durable stream.	Post now has cleanup-backed partial unique (groupId, source, sourceChannelId, sourcePostId) for rows with complete source identity, and the importer catches only that duplicate-key error before updating/reusing the existing post. Source-identity create/update/delete transitions append PostEvent rows inside the post DB transaction. The migration preserves legacy duplicate post rows by keeping the oldest row as the source identity owner and detaching later duplicates from sourcePostId. Focused regressions cover source-identity upserts that move reply/repost targets, replace content size without double-counting availability, move an imported post from published to draft, mark an imported post deleted while removing it from the group manifest, and persist the deleted import event.	Keep the DB idempotency path, then add full edit policy, richer event payloads, and legacy detached duplicate review so remote lifecycle history is fully replayable instead of only partly represented by the latest mutable row.
P2	Category feeds have cursor pages and optional totals, but legacy offset migration remains.	groupCategory.getCategoryPosts now keeps Post -> Group -> categories in the page-ID selection query, supports (publishedAt,id) cursor filters that skip total, supports includeTotal=false for offset callers that do not need counts, projects only post id/order plus the required group id alias, then hydrates contents/group only for the bounded post IDs. Category/section/admin/member pivots have explicit model indexes and a concurrent production migration. Old offset callers that need exact totals still pay a count.	Prefer cursor parameters or includeTotal=false for large category feeds. Keep content/body hydration page-scoped and cache or explicitly request totals before large public category browsing depends on exact counts.
P2	Text/json content projection reads payloads from storage during list/render paths.	group.prepareContentData now accepts projection options so callers can keep text/JSON bodies as metadata or share a bounded bodyTextCache across one render/feed. RSS uses that path to read only the selected feed text body while leaving JSON and non-feed text attachments unhydrated, and static-site generation passes one bounded body cache through content-list and group-post rendering. Broader API/body projection remains a follow-up.	Keep separating metadata list projections from body hydration. Use the bounded per-render/feed body cache for repeated snippets; add persisted snippet metadata only if real-site runs show unique body reads, not repeated reads, as the pressure point.
P2	Mutable row updates still lack protocol-grade revision payloads.	Canonical post create/update/delete writes now append post_lifecycle events, and source-identity imports append source_import events. Static publishing, delivery state, and future ActivityPub/E2EE still mostly map back to mutable Post rows and refreshed manifests.	Extend the post event/revision layer before deep federation or chat work: encrypted envelope updated, manifest generated, federation delivered, retry status, and payloads rich enough for replay.
P2	Production schema rollout is still incomplete for remaining constraints/backfills.	Recent slices added Postgres migrations for timeline/content/quota/static-ID indexes, BIGINT size columns including category/section aggregate sizes, actor-scoped content manifest lookup, content soft-delete columns, cleanup-backed user-limit uniqueness, cleanup-backed post-content position uniqueness, cleanup-backed group-local post uniqueness, cleanup-backed social-import post identity uniqueness, cleanup-backed file-catalog active path uniqueness, cleanup-backed pin-account owner/name uniqueness, async-operation/operation-queue/auto-action/static-rebind/category-pivot lookup indexes, the auto-action user-list index, AutoAction execution-claim columns/index, auto-action log payload TEXT columns, the creator-owned group-list index, content-bot user-list index, object-cache key correction, and bounded page hydration. The attempted active-row same-user content storage uniqueness blocked production startup on legacy duplicate data and has been reverted. New tables such as StaticIdBinding are model-sync-created instead of migration-created. The remaining constraint work still needs duplicate cleanup/backfill first, especially any future manifest uniqueness constraint.	Treat every remaining existing-table index/constraint/type change in this review as a real migration with duplicate cleanup, backfill, CONCURRENTLY/low-lock planning for Postgres, validation queries, and rollback behavior. Do not create migrations only for brand-new tables, and do not swallow migration errors that should block rollout.
P2	Static-ID history can grow with every manifest update.	updateGroupManifest binds changed group manifests to group.manifestStaticStorageId; bindToStaticId records StaticIdHistory audit rows and upserts StaticIdBinding as the one-row current state. Hot static/dynamic resolution now prefers the compact binding table and falls back to history with lazy current-row fill for model-sync upgrades, guarded so old dynamic IDs do not revive stale bindings. New writes compact stale rows for the touched static ID in bounded batches while preserving the current binding; existing large histories can be drained explicitly after backup through the opt-in startup batch.	Tune the retained-row count for production audit needs. If full static-ID audit history becomes product-visible, archive old rows before compaction rather than treating StaticIdHistory as permanent storage.
P2	Upload quota checks still have a pre-stream reservation gap.	getUserLimitRemained now has a (userId, name, createdAt) ledger index, the ledger size is BIGINT, and UserLimit is unique per (userId, name) with duplicate-key retry in the setter. addContent now commits the Content row and upload UserContentAction together through addContentWithUserContentAction, which locks the active user-limit row and re-checks upload/pin totals before the visible upload is recorded. Parallel uploads can no longer both commit past the same active limit, but the stream/storage write still starts before this final locked check.	Keep the locked commit path. For large files and expensive remote pins, add a real reservation/release ledger entry before streaming and reconcile failed or abandoned uploads.
P2	Reply/repost counters have a shared repair helper, while richer replay history remains.	reconcilePostRelationCounters recomputes both repliesCount and repostsCount for target posts and is used by createPost, updatePost, deletePosts, and remote group manifest stale-ref tombstones inside their canonical transactions. Focused tests cover direct repair, create/update/delete relation changes, source-identity import upserts that move reply/repost targets, and remote group manifest edit/delete replay. Future group moves still need explicit lifecycle semantics and are currently rejected rather than supported.	Keep create/update/delete, source-identity upserts, and remote group replay tombstones on the shared repair helper. Add richer event payloads and legacy detached duplicate review before treating remote/social import history as fully replayable beyond the latest mutable row.
P2	Static-site/RSS generated output still has heavy body/copy work even after DB hydration is batched.	Static-site generation now scans lightweight post refs in cursor batches, renders availablePostsCount as the public total, hydrates each bounded batch, streams SSR through current page/post state, reuses a bounded per-render text/json body cache, and reuses converted reply/repost post objects by DB id without retaining the whole generated site. RSS defaults to a smaller feed window, caps explicit archive reads at the legacy maximum, scans lightweight refs in batches, reads only the selected feed text body plus image metadata for each item, and reuses a bounded feed-local body cache. The generated-output pressure report can run against the synthetic fixture or a restored group and separates static text/json body candidates, RSS selected text reads, non-text copy candidates, and group-manifest inline/index pressure.	Keep generated-output DB loading and SSR state page-scoped. Remaining large-output pressure is media copy work, genuinely unique post bodies, and manifest root payload policy; use npm run database:scalability:generated-output after the migration rehearsal and add persisted snippets or a default chunked-only manifest cutoff only when restored-data pressure supports it.
P2	Existing async-operation tables now have bounded retention cleanup, but history semantics are still intentionally small.	UserAsyncOperation is queried by findAll({where: {userId, inProcess?, name?}, order: [['createdAt', 'DESC']], limit: 10}); closeAllAsyncOperation marks rows in process during restart as stopped, and cleanupFinishedAsyncOperations deletes old inProcess=false rows plus linked and closed orphan queue rows in capped startup batches. The ledger has (userId, inProcess, name, createdAt) and (inProcess, updatedAt) indexes; UserOperationQueue has (module, isWaiting), (module, inputHash, isWaiting), (asyncOperationId), and (isWaiting, asyncOperationId, updatedAt, id), and its user-visible waiting list is page-capped.	Keep startup cleanup bounded. If operation history becomes user-visible beyond the latest 10 rows, make retention configurable and add cursor/history semantics instead of treating the ledger as permanent audit storage.
P2	AutoActions executor now has DB-backed claims, but very long job semantics still need policy.	claimAutoActionsToExecute atomically updates due rows selected with FOR UPDATE SKIP LOCKED, records executeClaimedAt and executeClaimExpiresAt, and CronService queues only claimed rows once the migration is applied. Claim columns are schema-detected at model load so new code can still start against an unmigrated old table and use the prior bounded read path until the migration/restart activates claims. The claim expires so a crashed worker does not orphan work forever. CronService still de-dupes queued/running IDs inside one process.	Tune the claim TTL for real production job durations, keep AutoAction functions idempotent where possible, and add job-specific heartbeat/extension semantics before long external imports or auto-tag jobs can exceed the default claim window.
P3	PinAccount ownership and discovery semantics are explicit; creator metadata remains overloaded only for job attribution.	Direct-user lookups require groupId IS NULL. Group-scoped credentials are controlled by current group editors, survive creator/admin rotation, and cannot be moved between scopes by update. Their creator userId remains audit metadata and owns existing auto-action rows, but grants no group-account access. Deletion retains historical ledger rows and never implies remote unpin. Automatic policy discovery walks stable (name, id) cursor batches, including legacy unnamed rows in an id-ordered final phase, so policies beyond 100 rows are not ignored.	Preserve scope-based authorization and complete traversal. A future durable worker/account-principal model may replace creator-based auto-action attribution, but must not weaken current group policy checks or rewrite historical pin ownership.
P2	The disabled-by-default static-rebind cron is bounded, but still a polling design if re-enabled.	app/modules/group/cron.ts still has the cron.schedule('* * * * *', updateStaticIdsOfGroups) call commented out. getGroupWhereStaticOutdated filters staticStorageUpdatedAt < now - X plus isDeleted: false, orders by (staticStorageUpdatedAt ASC, id ASC), and caps each batch with STATIC_REBIND_BATCH_LIMIT/default 100. The Group model and production migration cover (isDeleted, staticStorageUpdatedAt).	Keep the capped oldest-first scan for manual/ops use. Do not re-enable a minute-level cron without a load decision; prefer an event-driven rebind queue or a worker that records cursor/progress and backpressure.
P3	The Object table is an active data-structure cache, but it is not the canonical storage-object model.	app/index.ts uses getObjectByStorageId/addObject from getDataStructure and saveDataStructure; the cache now stores the same resolveProp value used for lookup and enforces unique (storageId, resolveProp) after removing the old storageId-only uniqueness.	Keep it as a narrow cache for data structures. Do not evolve it into A2 physical storage metadata; create a dedicated canonical contentAssets/storageObjects table when that carve-out becomes necessary.
P3	Public list parameters now support endpoint-specific sort allowlists, optional post-feed totals, and smaller public caps, but not every endpoint uses them yet.	helpers.prepareListParams and setDefaultListParamsValues now normalize limit/offset, restrict sort direction to ASC/DESC, reject unsafe sort column names, sanitize includeTotal, accept endpoint allowedSortBy policies, cap public group/category post feeds, user member/admin/personal-chat group lists, user friend lists, category group/section management lists, static-site management lists, operation-queue lists, invite lists, API-key management lists, admin user/group/content directory lists, auto-action management lists, pin account lists, foreign-account API lists, social-account API lists, and content-bot API lists at 100 rows, and cap user file-catalog browsing at 200 rows with known folder sort fields. Group/category post feeds use includeTotal=false to skip count queries on offset pages. Group directory lists now add a stable id tie-breaker, and the personal-chat creator/type/deleted default order is index-backed. The broader 10k ceiling remains available for explicit internal/batch/export callers, and file-catalog folder publish now uses a dedicated internal batch traversal instead of public browsing pages.	Continue applying endpoint-specific sort allowlists, deterministic tie-breakers, optional totals, and lower public page-size caps to other public browse APIs. Reserve 10k windows or dedicated cursor/batch walkers for explicit export/batch jobs rather than default API browsing.
P3	Tag, mention, and auto-tag tables now have lookup indexes, but feed semantics are still undefined.	Tag, TaggedPosts, Mention, and AutoTag now declare explicit lookup indexes, and the production migration adds them concurrently. AutoTag also points its groupId back-reference at Group rather than User. They are still quiet tables with no large-feed cursor/count policy or EXPLAIN baseline.	Keep the indexes, then define tag/mention feed APIs with stable cursor ordering, bounded page sizes, and large-fixture EXPLAIN coverage before using these tables for search/federation timelines.

Required Components To Add Or Clarify

The current schema should not be thrown away. It should be completed with these missing components:

Component	Why It Is Needed	First Shape
Post write transaction boundary	Prevent partial DB state when post/content linking or local ID allocation fails.	createPost, the publish/delete/status transition in updatePost, deletePosts, and remote post manifest imports now transaction-scope their canonical DB changes: local ID allocation, post row create/update, tombstone flag, PostsContents, size, shared reply/repost count repair, group counters, and post event rows. Social import create races now converge through a source-identity unique retry, relation target moves, content-size replacement, published -> draft, and isDeleted tombstone reconciliation through that retry are covered, and remote group manifest replay now tombstones removed/replaced refs before replacement import. Remaining coverage: derived manifest/static jobs after commit and richer remote lifecycle history.
Manifest generation job/state	Prevent storage/static manifest failures from breaking canonical post writes.	manifestStatus, manifestError, or async-operation backed queue for post/group manifest regeneration, layered on the existing indexed and retention-cleaned UserAsyncOperation/UserOperationQueue tables once retry semantics are explicit.
Manifest tombstones and chunked post index	Keep distributed manifests correct under deletes/edits without rewriting one huge object.	Delete-triggered and published -> draft/unpublished manifest regeneration now unset local IDs, remote group manifest replay tombstones removed/replaced refs while freeing reused remote local IDs for replacement rows, local generation can publish chunked-only postsIndex manifests, and existing paged indexes now rewrite only touched pages. Remaining shape: richer replay events and durable async workers that own retries/status instead of running generation inside user-facing writes.
Unique group-local post identity	Keep manifests and static paths stable under concurrent posting/imports.	Counter allocation is row-locked and folded into create/update DB transactions, the allocator self-heals from indexed MAX(localId), and cleanup-backed unique (groupId, localId) prevents duplicate canonical IDs. Gap policy: unique/stable, not gapless.
Post/content attachment constraints	Make ordered attachments reliable and fast.	The through-table primary key already prevents duplicate (contentId, postId) rows, cleanup-backed unique (postId, position) is present, setPostContents deletes obsolete positions before inserting replacements, and attachment resolution now returns explicit permission reasons. Attachment ordering constraints are covered; remaining work is body projection and any future explicit share/group-owned attachment policy.
Content identity and ownership constraints (A1)	Avoid accidental cross-user attachment while tolerating legacy duplicate storage rows.	(userId, manifestStorageId) lookup is indexed for actor-scoped manifest claim/import, (storageId, userId) lookup is non-unique because production restores contain same-user duplicates, soft-deleted rows do not block same-user re-upload, and ownerless imports cannot create new library rows. Remaining shape: define canonical asset rows and global/canonical lookup semantics in A2. storageId stays the physical object identity, not a globally unique library-row identity.
Attachment authorization resolver	Ensure posts only attach content the actor can legitimately use.	The first resolver is in place for create/update: DB IDs require owner/public and return owner/public reasons, manifest IDs create/reuse actor-scoped rows with an actor-manifest reason, and storage IDs are actor-scoped with an actor-storage reason. Future shape should add explicit group-owned/share/admin import reasons before those policies are accepted.
Remote content ownership boundary	Keep remote manifest import from collapsing different users into one arbitrary Content row.	Actor-owned remote import is now explicit when userId is present, and ownerless remote import can only reuse an existing shared row. Continue carrying the importing actor/group through manifest import, avoid persistent userId = null user-library rows, and define whether true ownerless remote content creates a canonical asset plus ownership row.
Published visibility contract	Prevent draft/queued rows from leaking into public timelines, generated static output, RSS, or unread counts.	Separate public/feed query helpers from admin/editor query helpers. Public helpers default to status: Published; admin helpers require explicit status filters.
Physical storage reference tracking	Prevent one user's delete/unpin from breaking another user's row, a post attachment, a preview object, a recorded pin, or a generated manifest/static site.	database.getContentDeleteSafety now centralizes the DB row refs, same-storage/preview refs, all known DB-visible derived storage columns including current static-ID dynamic targets, durable preview/generated-output child refs whose source ancestor is still DB-visible, canonical StorageObject.isPinned, and recorded remote pin refs used by fileCatalog.deleteFileCatalogItem(..., {deleteContent}) while retaining legacy Content.isPinned row preservation. database.getStorageObjectDeleteSafety exposes the storage-only half for GC workers, and the storage-space module queues distinct original/preview physical IDs through the shared async-operation runner with configurable retention delay, an optional bounded worker, a final delete-safety recheck before unpin/remove, and AdminRead storage-removal history. Unreferenced content rows are now soft-deleted before physical cleanup, normal reports ignore hidden content, and the active-row uniqueness predicate allows same-user re-upload of the same storage ID. Admin tombstone routes can list/restore hidden rows while physical storage still exists, preview purge candidates after CONTENT_TOMBSTONE_RETENTION_DAYS (default 30), and hard-delete only expired tombstones whose physical storage is already missing and whose content row has no active DB references; upload accounting rows survive with contentId detached. The storage-space module can inspect and reconcile bounded recursive generated-output DAG children into StorageObject metadata plus StorageObjectReference edges, storage-object integrity repair can recreate preview objects/edges from restored Content, the pinned-object drilldown shows remote pin counts, and database:storage-space-report lets restored-data rehearsals persist the same active-content analyzer numbers before cleanup policy changes.
Incremental group counters/size	Avoid full Post scans on every post create/update inside updateGroupManifest.	availablePostsCount and size now move incrementally, create/update/delete counter effects are transaction-scoped with the post write, source-identity import upserts have regression coverage for content-size replacement without double-counting availability, remote group manifest edit/delete replay repairs counters after tombstones/replacements, and reconcileGroupCounters has drift-repair coverage for size, availability, and the local-ID high-water mark. Static-site output now treats availablePostsCount as the public count and keeps publishedPostsCount internal to local-ID allocation/import. Full SUM/COUNT work stays reserved for explicit repair; remaining work is eventually renaming or splitting the legacy high-water field.
Production migration/backfill playbook	Make the review actionable for existing local/server nodes, not only fresh databases.	Decision: target Postgres only (SQLite path stays dropped). Production data exists, so every existing-table index/uniqueness/type change ships as a real migration with cleanup SQL, backfill, CONCURRENTLY/low-lock planning, verification query, and rollback notes. Brand-new tables are created by Sequelize model sync and should not get migrations just for creation. Source-identity cleanup deliberately preserves all post rows by detaching later duplicates from the retry key instead of deleting them. Migration errors fail loudly and block deployment — do not copy the swallowed .catch((e) => console.error(e)) pattern from change-size-type.cjs.
Static ID current binding and retention policy	Avoid unbounded static-history growth from manifest churn.	StaticIdBinding is now the model-sync current row/table for hot resolution, and StaticIdHistory remains a bounded audit trail. Missing current rows on upgraded databases are lazily filled from history. New binds compact stale rows for that static ID, and opt-in startup batches can drain old histories after backup. Remaining shape: archive policy if product requirements need full historical static-ID timelines.
Quota/action ledger indexes and reservations	Keep uploads cheap and enforce limits under concurrency.	(userId, name, createdAt) is present for ledger sums, UserLimit(userId, name) is cleanup-backed unique, and ledger sizes are BIGINT. Upload content rows and upload actions now commit under a locked limit re-check, so concurrent uploads cannot both become visible past the limit. Remaining shape: pre-stream reservation/release entries for large files, remote pins, and failed upload cleanup.
Deterministic post ordering	Stable timeline pagination and cursor scanning.	Group and category timeline pagination now use id as the secondary sort key alongside publishedAt, and unread cursors can store readPostId next to readAt; remaining alignment work is any export/import scans that still rely on offset or timestamp-only state.
Publish/move state machine	Avoid derived-state writes for drafts and broken local IDs during group moves.	Draft -> published, published -> draft, and published -> deleted now have counter and manifest effects, and source-identity import retries cover both published -> draft and published -> deleted paths. Source-identity create/update/delete transitions also append PostEvent rows. Remaining shape: optional group move or explicit prohibition, plus broader non-import post event coverage.
Post revision/event table	Support edits, deletes, ActivityPub updates, social import reconciliation, and E2EE envelope lifecycle.	The model-sync-created postEvents table stores canonical post lifecycle events and source-identity import events with actor, post, group, source identity where present, previous/next status, and deletion state. Manifest generation, delivery state, and richer replay payloads remain.
Remote import test path	Ensure distributed manifests reconstruct local DB state correctly.	Remote post manifest import now has Docker-backed coverage for post manifest -> content manifests, target group/user state, published/source dates, ordered attachment views, actor-scoped content rows, group counters, group manifest refs, post lifecycle/source-import events, concurrent same-manifest import retry idempotency, group manifest -> post iteration, and remote group manifest edit/delete replay. Remaining coverage: async derived-state retry behavior and large-manifest/chunked-index fixtures.

Core Scalability Phase Closeout

The core database/storage scalability phase is complete for now. Treat the following as repeatable verification and rollout guardrails, not blockers for starting the next TODO items; reopen this phase only when a restored dump, migration audit, production DHT cost, or correctness issue exposes a real regression:

1. Rehearse the May 2026 migration chain on a restored Postgres backup with npm run database:migration-rehearsal; fix only audit/migration issues found there. Status: the May 15, 2026 content-only dump passed, then the May 15 posts dump exposed a real DB counter drift that is now repaired by 20260515000000-backfill-post-and-group-size-counters.cjs and covered by the integrity audit. The rehearsal now also syncs model-created tables and runs storage-object reconciliation before the final audit so restored Content rows have canonical StorageObject rows. Rerun this command when another production dump or new migration appears. When the restored dump includes groups/posts, follow with npm run database:derived-state-integrity so manifest/static derived-state gaps are reported separately from DB-only migration invariants.
2. Run the generated-output pressure pass against a restored group after the migration rehearsal: CONFIRM_RESTORED_BACKUP=1 DATABASE_NAME=<restored-db> RESTORED_GROUP_ID=<restored-group-id> npm run database:restored-pressure (or use RESTORED_GROUP_NAME when the restored name is unique). Status: the May 15 posts dump passed restored pressure for group id 1 with GENERATED_OUTPUT_POST_LIMIT=20; keep rerunning this for larger restored groups. The EXPLAIN probes distinguish hot paths from explicit repair/fallback paths, including durable manifest cursor scans, current static-ID binding lookup, static-ID history fallback, and group counter reconciliation. On restored production-shaped data, optional category/content/static-ID probes are skipped when no sample rows exist instead of failing the whole run. Body projection now has bounded per-render/feed caches, and the report includes group-manifest inline/index pressure; add persisted text/json snippets only if measured pressure is from many unique body reads rather than repeated reads or non-text copy work, and choose a chunked-only manifest cutoff from restored-data manifest pressure rather than guessing.
3. Shared content lookup policy is locked for A1: public metadata is projected, attachment resolver reasons are explicit, and new ownerless library-row creation is blocked until canonical asset policy is wired through StorageObject identity or a later asset table. Future share/group/admin attachment reasons belong to feature-driven policy work.
4. The group-derived-state queue now covers post/group manifest rebuild jobs by default with checkpoint output, attempt-aware failure messages, bounded kick batches, and an interval worker. npm run database:derived-state-async-rehearsal is available for guarded restored-data runs with GROUP_DERIVED_STATE_ASYNC=1; the May 15 posts dump passed with one queued repair and final integrity at zero issues. Keep GROUP_DERIVED_STATE_ASYNC=0 as the rollout escape hatch and rerun the rehearsal on larger restored dumps when available.
5. RSS now defaults to a smaller feed window while preserving the legacy 9999-item maximum for explicit archive reads. Further feed-size changes are product policy, not core scalability blockers.

Latest synthetic measurement: on May 14, 2026, a fresh Docker Postgres run completed database:sync-models, the default database:scalability:fixture shape (100k posts, 1k content rows), database:scalability:explain, and database:scalability:generated-output. The EXPLAIN report had no Seq Scan mentions; expected expensive probes were bounded/known paths (Timeline page (Published, offset pagination) 8.107 ms, Unread count 11.694 ms, Category feed page 26.345 ms, Group counter reconciliation scan (repair-only) 15.460 ms), while the cursor/static-ID/manifest probes stayed sub-millisecond. The generated-output report measured 6,030 attachment rows over the newest 9,999 posts, 995 unique attachment storage IDs, 1,516 static text/json body candidates with 247 unique bodies, 4,886 non-text copy candidates with 807 unique storage IDs, and 100k group-manifest refs with a 3.4 MB inline-reference lower bound / 100 postsIndex pages. This validates the synthetic fixture path, but it should not replace the restored-backup rehearsal or restored-group pressure run before changing production defaults.

Latest restored production rehearsal: on May 15, 2026, geesome-prod-2026-05-15.dump (custom-format dump created May 14, 2026 23:25 UTC from Postgres 14.13) was restored into disposable Docker Postgres database geesome_prod_rehearsal_20260515. The dump started with 49 public tables and only two recorded migrations; npm run database:migration-rehearsal applied the May 2026 migration chain, synced model-created tables, and finished with database:migration-integrity reporting 185 passed, 0 failed, 0 skipped. The migrated rehearsal database had 25 SequelizeMeta rows, 4 users, 36,694 content rows, 41,888 file-catalog rows, 0 groups, and 0 posts. This validates the migration chain against real production content/file-catalog data, including duplicate-cleanup and final-state integrity checks, but it cannot answer restored large-group generated-output pressure because no group/post rows exist in that dump.

Latest restored posts rehearsal: on May 15, 2026, geesome-prod-with-posts-2026-05-15.dump was restored into disposable Docker Postgres database geesome_prod_posts_rehearsal_20260515. The restored pre-migration data had 4 users, 36,698 contents, 41,891 file-catalog rows, 1 group, and 2 published posts. It exposed a legacy partial write: post id 1 had two attached content rows totaling 370038 bytes but posts.size IS NULL, while group id 1 still had groups.size = 11; both posts had valid publishedAt values, so the UI "Invalid date" evidence was not caused by a null DB publish timestamp. After adding the size/counter backfill migration and audit checks, a fresh npm run database:migration-rehearsal finished with database:migration-integrity reporting 187 passed, 0 failed, 0 skipped. The repaired state has post sizes 370038 and 11, group size 370049, publishedPostsCount = 2, and availablePostsCount = 2. CONFIRM_RESTORED_BACKUP=1 DATABASE_NAME=geesome_prod_posts_rehearsal_20260515 RESTORED_GROUP_ID=1 GENERATED_OUTPUT_POST_LIMIT=20 npm run database:restored-pressure also passed. The dump still shows post id 1 with manifestStorageId IS NULL; that is derived manifest/static state, not a DB-only migration invariant. database:derived-state-integrity now reports that class of issue explicitly, and CONFIRM_DERIVED_STATE_REPAIR=1 DERIVED_STATE_REPAIR=1 DATABASE_NAME=<restored-or-backed-up-db> npm run database:derived-state-integrity can regenerate missing local post manifest/directory state and missing group manifests through the normal manifest paths. On May 15, 2026, the same posts dump was restored again into geesome_prod_posts_async_rehearsal_972, migrated with DATABASE_PORT=55432, and then run through CONFIRM_RESTORED_BACKUP=1 DATABASE_NAME=geesome_prod_posts_async_rehearsal_972 RESTORED_GROUP_ID=1 npm run database:derived-state-async-rehearsal against Kubo RPC storage. The async rehearsal found one missing public post manifest and one missing local post directory, queued post id 1, drained one group-derived-state job in bounded batches, and finished with all derived-state integrity counts at zero. Future restored dumps with larger groups should repeat the same command, with STORAGE_MODULE / STORAGE_URL / STORAGE_REPO pointed at the restored or copied storage backend.

First Implementation Plan

1. Add read-only inventory checks so future model/query changes keep this review current.
2. Adopt the A1 content identity model: keep per-user Content rows, use non-unique (storageId, userId) actor lookup, leave same-storageId rows across different users and legacy same-user duplicates valid, and do not add global storageId uniqueness to the user-owned table. Funnel public serving and pin/refcount reads through shared helpers so the later A2 carve-out is mechanical. Status: per-user/shared helper seams, public GET/HEAD/ranged metadata reads, public metadata projection, deterministic shared storage/manifest reads, actor-scoped manifest lookup, remote user/group media actor propagation, ownerless-create rejection, canonical local pin state, nullable canonical storage-object identity metadata, and GeeSome content-manifest identity seeding are present; the attempted (userId, storageId) uniqueness was reverted after production duplicate data blocked startup.
3. Fix post attachment resolution so create/update/import paths cannot attach another user's content row accidentally. Add tests where two users upload the same bytes, then try to attach by another user's DB ID, by duplicate manifest ID, and through the remote/import claim path. Status: create/update now reject another user's DB ID, claim manifest IDs into actor-scoped rows, and return explicit owner/public/actor import reasons; broaden tests around remote group import and explicit share policies when those policies exist.
4. Add a physical-storage reference check before any delete/unpin/remove path. Start with fileCatalog.deleteFileCatalogItem(..., {deleteContent}), then cover content rows, post attachments, file catalog items, avatars/covers, static generated output, and pins. Status: file-catalog delete now preserves referenced or legacy pinned Content rows and gates physical removal through the reusable database.getContentDeleteSafety decision on same-storage, preview, all known DB-visible derived storage columns, current static-ID dynamic targets, canonical StorageObject.isPinned state, recorded remote pin refs, and durable bounded recursive generated-output DAG refs; delayed GC remains.
5. Make public/feed/static/RSS/category post loading published-only by default, and split editor/admin draft access into explicit APIs or filters.
6. Fix the remote content import object handoff, remote post group/user/published-time propagation, and the remote-post setPostContents call shape. Add manifest import tests for posts with ordered contents, duplicate storageId ownership, and the cold-cache createContentByRemoteStorageId path so existing imported manifests cannot mask the bug. Status: actor/group context, attachment shape, cold-cache object handoff, post manifest source/date rehydration, canonical remote post DB state, concurrent same-manifest retry idempotency, group-manifest post iteration, and ownerless-create rejection are covered; true canonical asset semantics remain A2 backlog.
7. Define the post lifecycle state machine: drafts/queue are DB-only, publish allocates localId and manifests, delete/unpublish unset manifest refs, and group moves are either prohibited or fully transactional. Status: localId is unique/stable but not gapless, deletePosts, published -> draft/unpublished, and source-identity isDeleted upserts now unset local IDs during group manifest regeneration, source-identity imports append PostEvent lifecycle rows, and group moves are rejected; broader non-import lifecycle events still need coverage.
8. Make post creation/update/delete DB changes transactional, starting with local ID allocation, post row creation, PostsContents, size/counter updates, reply/repost counters, and old/new group reconciliation. Status: create, publish-transition update, status-boundary reply/repost repair, delete, source-identity import upsert relation/content/status/delete moves, and remote group manifest edit/delete replay now share the canonical post-state paths where they mutate DB state; manifest/static derived work remains.
9. Stop recomputing Group.size and availablePostsCount via full Post scans on every post write; maintain them incrementally inside the post-write transaction, with an explicit reconciliation command. Status: create/update/delete now keep group size and availability counter effects inside the post transaction; source-identity import upserts are covered for size replacement without availability double-counting plus published -> draft/deleted decrements; reconcileGroupCounters is covered for stale size, availability, and local-ID high-water repair; post reply/repost counters have a shared repair helper; remote group manifest edit/delete replay reconciles counters after imports.
10. Add production migrations, not just model index definitions, for the safest missing indexes first: post timeline (groupId, isDeleted, status, publishedAt, id), manifest differential rebuild (groupId, status, updatedAt, id), post local lookup, post-content join order, content manifest lookup/ownership lookup, preview lookup, static ID dynamic lookup, quota/action ledger, social import idempotency, category pivots, and group permission/membership lookups. Status: the non-unique lookup/index portions are now broadly landed through the timeline/content/quota/static-ID/group/category/operation/tag/mention/file-catalog batches, and cleanup-backed uniqueness is present for user limits, post-content positions, group-local post IDs, social-import post source identity, and file-catalog active paths. Same-user content storage uniqueness was reverted because restored production data contains duplicates; remaining future constraints include manifest uniqueness after semantics are locked.
11. Migrate aggregate size fields to BIGINT and clarify logical-vs-physical byte counters before large media imports. Status: hot post/group/content/quota fields and quieter category/section aggregate size fields are now BIGINT in models, production migrations, and migration-integrity checks. Remaining work is semantic/product policy only: decide which dashboards or billing/quota rules use logical attachment/library bytes versus physical storage bytes.
12. Add a large-group seed/benchmark fixture (confirmed and expanded) that creates at least 100k posts with attached content metadata in Docker Postgres and records EXPLAIN ANALYZE plans for timeline, unread, category feed, static-site, RSS, manifest, content-preview serving, quota checks, and static-ID resolution paths. Status: database:scalability:fixture seeds a reusable 100k-post group plus category, content preview, quota ledger, attachment, and static-ID side rows; database:scalability:explain samples real fixture rows and writes an intentionally uncommitted plan report against current hot paths plus explicit repair/fallback scans. Keep running it before/after future index or query migrations so plan regressions are caught against large data rather than small test tables.
13. Move post/group manifest regeneration to async derived-state jobs, with idempotent retries, import tombstone handling, chunked manifest-post indexes, and static-ID history retention behavior. Status: delete/unpublish manifest unsets, changed-ref cursor batching, durable group manifest post cursors, remote group import tombstones, model-sync static-ID current bindings, bounded static-ID history compaction, a dual-written chunked post-index sidecar with import fallback, default chunked-only generation above 1,000 posts, page-level post-index rewrites, an IPLD storage direction note, a manual database:derived-state-integrity verifier/repair path, and a default-on durable group-derived-state queue for post/group manifest rebuilds have landed. The queue records manifest/static-directory checkpoint output and attempt-aware retry errors, processing is bounded per kick with an interval worker, and the May 15 posts dump passed guarded async rehearsal. Remaining work is larger restored-dump reruns when available and optional static-history archiving.
14. Introduce cursor pagination beside existing offset pagination (with a stable (publishedAt, id) sort), then migrate high-volume callers and unread cursors. Status: getGroupPosts and getCategoryPosts have cursor pagination plus includeTotal=false for offset callers that do not need counts, group/category/global post listings select page IDs before hydration, global post scans have cursor-aware lightweight refs and a batch walker, social-import reversal scans refs in cursor batches, and unread counts can use (readAt, readPostId) when clients send the post cursor; remaining high-volume callers still need migration.
15. Split heavy generated-output paths into batched post-ref scans and page-scoped content/body hydration. Status: API group/global/category post listings hydrate only the bounded page, group-manifest generation uses cursor-batched lightweight post refs, and static-site/RSS generated output hydrates post-ref batches. RSS feed items now keep JSON/non-feed text metadata-only, read only the selected feed text body, default to a smaller feed window with an explicit legacy-capped archive limit, and reuse a bounded feed-local text body cache. Static-site generation streams SSR through current page/post state instead of materializing one final posts[], display totals now use availablePostsCount rather than the local-ID high-water mark, text/json body reads share a bounded per-render cache, and repeated reply/repost objects are reused within one render. The generated-output pressure report measures body-read uniqueness, non-text copy candidates, and group-manifest inline/index pressure on fixture/restored data. Group manifests dual-write a chunked post-index sidecar and omit the legacy inline trie by default above 1,000 posts; persisted text/json snippets should be driven by measured restored-data pressure.
16. Add tag/mention/auto-tag indexes before exposing those dimensions as timeline filters or federation/search features.
17. Add a post event/revision layer before ActivityPub federation, durable chat, and social import reconciliation become deeper features. Status: the first PostEvent table, ordinary post_lifecycle events, and source-identity source_import events are present; manifest/static job events, delivery state, and richer revision replay payloads remain.

Backlog (Future Slices)

These are committed destinations beyond the current implementation plan. They should not block the slices above but should be visible whenever the affected modules get touched, so the A1-era helpers and indexes are shaped to make them mechanical landings rather than rewrites.

• A2 carve-out: canonical storage-object behavior. A model-sync StorageObject registry now records one physical metadata row per storageId from new content writes, remote GeeSome content manifest imports seed geesome-content-manifest identity before actor-scoped library creation, database callers can resolve canonical rows by identity pair, and shared serving metadata prefers storage ID metadata. Local pin state remains on the canonical row, while remote attempts and confirmed claims live in the per-account PinStorageObject state machine; provider acceptance alone does not set the local pin bit. Restored-data repair can backfill or refresh canonical original/preview rows and preview edges from existing content, AdminAll storage-space repair can persist measured generated/static output refs plus bounded recursive generated-output child refs, and delete safety includes all known DB-visible derived storage columns, current static-ID dynamic targets, protected remote-pin refs, and durable preview/generated-output child refs whose source ancestor is still visible. Physical removal now has a storageSpace queue with configurable retention delay, an optional bounded worker, a final getStorageObjectDeleteSafety recheck for each original or preview storage ID, and operator-visible history. Content remains the per-user ownership/library entry, unreferenced library rows are hidden with isDeleted/deletedAt rather than hard-deleted, and operators can restore a tombstone before storage disappears when no active same-storage row already replaced it. Expired tombstones can now be previewed and purged only after physical storage is already missing. Canonical storage objects now have nullable indexed identity metadata for ownerless/federated objects. The backend foundation is complete for now; remaining work is ActivityPub/generated producers, trust policy, and public UI/API semantics for that identity.
• Additional StorageObject identity producers. Generated/static outputs, ActivityPub objects, and other protocol-owned objects can seed canonical identity metadata after their product identity and trust policy is explicit. Do not add a new producer just because a storage ID exists; each producer needs a stable identity type, ownership/trust boundary, import/update semantics, delete-safety behavior, and public UI/API meaning.
• Global storage-object uniqueness. Only valid after A2; never on the A1 Content table.
• Post event/revision table. Trigger conditions: ActivityPub federation, durable chat, social-import reconciliation. Canonical post lifecycle and source-identity import lifecycle events are the first landed on-ramp; richer post revisions and delivery/manifest events remain future slices.

Target Index Candidates

These should be validated with EXPLAIN ANALYZE against a large fixture before landing production migrations. Items marked present already have model and migration coverage, but they still benefit from production-scale plan validation:

Table	Candidate Index	Supports
posts	(groupId, isDeleted, status, publishedAt, id)	published timeline pages, unread counts, static/RSS newest-first scans
posts	(groupId, status, updatedAt)	group manifest differential rebuild (updatedAtGte filter)
posts	(groupId, status, updatedAt, id)	manifest job cursor with deterministic tie-breaker
posts	(groupId, id)	social-import reversal scans and cursor iteration
posts	present cleanup-backed unique (groupId, localId)	group manifest/local post lookup and stable local identity
posts	(manifestStorageId)	post-by-manifest lookup (getPostByParams)
posts	present cleanup-backed partial unique (groupId, source, sourceChannelId, sourcePostId) where source fields are present	idempotent social imports and retry-safe source reconciliation
postEvents	present (postId, createdAt, id) and (groupId, createdAt, id)	per-post and per-group event replay/audit streams
postEvents	present (groupId, source, sourceChannelId, sourcePostId, createdAt, id)	source-identity import lifecycle replay and tombstone audit
postEvents	present (type, action, createdAt, id)	filtered event workers/reconciliation scans
postsContents	present cleanup-backed unique (postId, position)	content hydration in stable post order
postsContents	(contentId)	reverse content-to-post lookups
categoryGroups / category pivots	present: (categoryId, groupId) / (groupId, categoryId) on category admin/member/group/membership pivots	category feed joins and group/category administration
groupSectionsPivot	present: (sectionId, groupId) / (groupId)	section group listing and one-section-per-group lookup
groupPermissions	(userId, groupId, name)	user permission checks
groupPermissions	(groupId, userId)	group membership/permission administration
groupMembers	(userId, groupId) / (groupId, userId)	membership checks during permission gating
groupAdministrators	(userId, groupId) / (groupId, userId)	admin checks during permission gating
contents	(userId, createdAt, id)	user content library pages
contents	present non-unique (storageId, userId) lookup (A1)	actor-scoped lookup for user-owned library rows while allowing different users and legacy same-user duplicates to reference the same storageId; soft-deleted rows no longer block same-user re-upload, new ownerless library rows are blocked, and full canonical remote asset semantics move through StorageObject rather than a global or per-user Content.storageId constraint
contents	(userId, manifestStorageId) (A1; add uniqueness only after duplicate cleanup if row semantics require it)	actor-scoped manifest-based content lookup. (Note: manifestStaticStorageId is already covered by its unique: true constraint.)
contents	(largePreviewStorageId), (mediumPreviewStorageId), (smallPreviewStorageId)	public preview/header lookup when findByPreviews is true
contentDependencies	present unique (parentContentId, role, position) plus (childContentId, role, id)	immutable composite recipe slots and reverse delete-safety checks for original/SVG children
imageCompositionIdentities	present unique (userId, compositionId) plus (rootContentId)	content-first root convergence and reverse root-reference safety
imageCompositionOperations	present identity unique, pending-claim, result/candidate Content indexes	idempotent create/revision recovery, stale-claim takeover, and orphan candidate audit
objects	present unique (storageId, resolveProp)	getDataStructure cache rows for resolved path reads vs unresolved/root object reads
storageObjects	present unique (storageId) plus isPinned	model-sync canonical physical metadata and local pin registry for new content writes; do not add this uniqueness on the per-user Content table
staticIdHistories	(dynamicId, boundAt) plus bounded per-static compaction	getStaticIdItemByDynamicId, manifest/static lookup by dynamic ID, and stale generated-manifest history cleanup
staticIdBindings	present unique (staticId) plus (dynamicId, boundAt)	hot static-ID resolution without scanning long audit histories
userContentActions	(userId, name, createdAt)	quota SUM(size) checks during uploads/pins
userContentActions	(contentId)	content delete/reference and accounting lookups
userLimits	present cleanup-backed unique (userId, name)	admin get/set limit path and quota lookup
userAsyncOperations	present: (userId, inProcess, name, createdAt)	getUserAsyncOperationList filter+order
userAsyncOperations	present: (inProcess, updatedAt)	startup closeAllAsyncOperation sweep
autoActions	present: (isActive, executeOn)	scheduled-action executor executeOn <= now AND isActive
autoActions	present: (userId, createdAt, id)	user-facing auto-action management list default order
pinAccounts	present cleanup-backed unique (userId, name) / (groupId, name)	deterministic hot pin-account lookups and bounded owner-scoped account lists that do not include service in identity
foreignAccounts	present (userId), (userId, provider), and (provider, address)	bounded user-facing account lists, user/provider lookup, and admin provider/address lookup
socNetAccounts	present (userId, socNet, phoneNumber) and (userId, socNet, username)	social-network account matching plus bounded user-facing account lists
contentBots	present (userId)	bounded current-user content-bot setup lists before this table becomes a larger operational surface
groups	present: (creatorId, type, isDeleted, createdAt, id)	creator-owned personal-chat pages and getCreatorInGroupsByType lookups
groups	present: (isDeleted, staticStorageUpdatedAt)	the disabled updateStaticIdsOfGroups cron once re-enabled
fileCatalogItems	present: (contentId)	reference checks before content deletion/garbage collection
fileCatalogItems	present: (userId, parentItemId, isDeleted, type, createdAt, id)	large folder pages and active/deleted folder browsing
fileCatalogItems	present cleanup-backed unique (parentItemId, userId, name) for active child rows and unique (userId, name) for active root rows	exact active path writes; root rows need the separate index because parentItemId is NULL
taggedPosts	present: (postId, tagId) / (tagId, postId)	tag feed filters and post tag hydration
mentions	present: (sourcePostId), (targetPostId), (sourceGroupId), (targetGroupId), (creatorId)	ActivityPub/social graph and mention lookup paths
autoTags	present: (groupId) and required/result tag FK indexes	auto-tag rule lookup before large feed/tag automation

Migration Integrity Audit

Recent migration slices now have a DB-backed verification command:

npm run database:migration-integrity

For the full restored-backup rehearsal, use the guarded wrapper:

CONFIRM_RESTORED_BACKUP=1 DATABASE_NAME=<restored-db> npm run database:migration-rehearsal

After the migration rehearsal succeeds, run the guarded restored-group pressure wrapper:

CONFIRM_RESTORED_BACKUP=1 DATABASE_NAME=<restored-db> RESTORED_GROUP_ID=<restored-group-id> npm run database:restored-pressure

The migration rehearsal wrapper refuses to run without explicit restored-backup confirmation and an explicit database name; it also refuses DATABASE_NAME=geesome_node unless ALLOW_DEFAULT_DATABASE=1 is set. It runs npm run migrate-all-database, then npm run database:sync-models so model-sync-only tables such as current static-ID bindings and storage objects exist in the rehearsal target, then npm run database:storage-objects-integrity -- --repair so restored content rows have canonical original/preview storage-object metadata, preview edges, and pin state, and finally npm run database:migration-integrity. The restored-pressure wrapper uses the same database safety checks and additionally requires a group id or unique group name before writing the ignored EXPLAIN, generated-output, and storage-space analyzer reports. Prefer RESTORED_GROUP_ID for restored production copies because group names may be missing, renamed, or duplicated.

Run the integrity audit after taking a database backup and applying migrations to the restored/test target. The command connects to the configured Postgres database, verifies that every recent 202605... migration from known migration-capable modules is listed in the audit, checks that covered migrations are recorded in SequelizeMeta, then validates the resulting schema and data state. It fails if a required index is missing or invalid, a widened size column (including category/section aggregate sizes) is not BIGINT, userAsyncOperations.output or auto-action log payload columns are not TEXT, a cleanup-backed uniqueness invariant still has duplicates, a static-ID current binding is present but diverges from matching/latest history state, a post local-ID high-water counter regressed, a post size differs from its attached content size, group size/availability counters differ from active published posts, or a relation touched by the dedupe/backfill migrations points at a missing row.

When a migration fails the audit, restore the backup, adjust the migration or follow-up repair, rerun migrations, and rerun the audit before exposing the upgraded database to users. For model-sync test databases that did not run Sequelize migrations, use npm run database:migration-integrity -- --skip-migration-meta; production/upgrade rehearsals should not skip migration metadata. To validate brand-new model-sync tables, start the app or otherwise run model sync before the final-state audit.

Any new migration in a known migration-capable module at or after the May 2026 scalability migration floor must extend check/databaseMigrationIntegrity.ts with its expected indexes, columns, duplicate cleanup checks, relation checks, or explicit rationale. The coverage check intentionally fails when a new recent migration file appears without corresponding audit coverage. New tables created only by Sequelize sync should not be listed as covered migrations unless a real migration file exists; add model-sync final-state checks separately when useful.

Verification

• npm run database:scalability:update
• npm run database:scalability
• npm run database:sync-models
• npm run database:migration-integrity

This review now tracks the inventory, shipped index/query slices, and phase closeout. Future storage changes should be driven by restored-data measurements or feature-specific policy, while the next TODO work can move to ActivityPub/Fediverse, secure chat E2EE, and remaining product/API semantics.