# BE System Design — Social Schema Definition (v4) > ⚠️ **This is the v4 design. The shipped schema is v5** — read > [`schema-v5.html`](../../../guides/social-recording/schema.html) for the current shape. This document is kept because it > records why each v4 decision was made, and v5 was derived by re-judging those decisions one by one. > > ⚠️ **`object_raw` does not exist any more** (2026-08-07, commit `06e85b0`). The collection, the > `SocialObjectGraph.raw` field, and all wiring were removed. §1.8 below defines it as "lossless > originals" — that premise turned out to be false. What was stored had already been through the > SDK's transform; the untouched response never travelled above the SDK, so the one place the > schema allowed `Mixed` was resting on a claim that was not true. Every mention below is left in > place and marked. Rationale: > [`../social-generator/decisions.md`](../../../guides/social-recording/decisions.md) G-8 (retired) · G-12 (replacement). > > Implementation source of truth. Human-facing spec: [`schema-v4.html`](./schema-v4.html). > Source draft: `docs/drafts/define-schema-draft.md`. > **Upstream spec: `schema-v4.html` (cited below as v4 §n). The earlier v3 spec and the v3-based revision of this > document are superseded and must not be consulted — where they disagree with v4, v4 wins by definition.** ## 0. Scope & Context **Purpose.** Define the MongoDB persistence layer for the social-signal domain: the collections that hold social content, creators, venues, tokens, and the observation logs that connect them — plus the typegoose models and repositories that own access to them. **In scope (this iteration)** | # | Deliverable | Boundary | |---|---|---| | 1 | 7 collections + indexes | Schema definition only. No migration scripts, no seed data. | | 2 | 7 typegoose models (rich) | Static factories, identity rules, named state-change methods. | | 3 | 7 repositories | Pure data access. No business logic, no transaction session parameter. | | 4 | 1 domain module | `MongooseModule.forFeatureAsync` registration + provider exports. | The draft fixes the boundary explicitly: *"구현 규모가 크기 때문에 일단 schema 정의까지만 진행하고 별도의 service, controller 등은 제외"*. Consequently `ContentGenerator` (the adapter that turns a fetch result into these objects) and every service/controller are **out of this iteration** and appear only as callers. **No migration.** The collections hold no production data, so this revision **replaces** the previous schema rather than migrating it. Nothing reads these collections outside the module's own tests — verified by search: the only non-module references are in `test/`. **Stack.** NestJS 11 · MongoDB 7.0 · Mongoose 8 · `@typegoose/typegoose` 12 · vitest. **Repository this schema belongs to.** Price / OHLCV / `alpha_score` remain owned by `sol-alpha-finder-tracker` and are fetched over the wire, never copied here. ### Non-functional requirements (Phase 1.5) None beyond platform defaults. Known volumetrics, recorded as context rather than as targets: `token_links` ≈ 10–20K/day · 100–200 metric points per content · 7,742 distinct URLs measured in the audit. ### Cross-cutting aspects (Phase 1.6) Two categories were signal-detected and both were **explicitly excluded** by the user. See [§6 Out of Scope](#6-out-of-scope-explicit-exclusions) for the audit record and the consequences. --- ## 1. Database Schema ### 1.1 Entity relationships ```mermaid erDiagram accounts ||--o{ contents : "creator_id" accounts ||--o{ venues : "creator_id" venues ||--o{ contents : "venue_id" contents ||--o{ contents : "parent_content_id" tokens ||--o{ token_links : "token_address" contents { ObjectId _id PK string platform string subtype string platform_key array source_urls ObjectId creator_id FK ObjectId venue_id FK ObjectId parent_content_id FK string parent_relation Date published_at Date observed_at object text array links array tags array mentions object author object metrics_latest object link_stats object data } accounts { ObjectId _id PK string platform string platform_key array source_urls array handles Date account_created_at string display_name string bio array links array declared_handles array known_wallets object metrics object moderation Date observed_at object data } venues { ObjectId _id PK string platform string subtype string platform_key array source_urls string name string description Date venue_created_at ObjectId creator_id FK object metrics Date observed_at object data } tokens { ObjectId _id PK string address UK string symbol Date first_transfer_at Date discovered_at array fingerprints object web } token_links { ObjectId _id PK string token_address FK string object ObjectId object_id string source_url Date linked_at Date observed_at string status boolean name_match string platform string subtype Date first_transfer_at array lineage number link_depth } metric_series { ObjectId _id PK string object ObjectId object_id UK string platform array points } object_raw { ObjectId _id PK string object ObjectId object_id UK object payload Date observed_at } ``` ⚠️ Two satellites remain, and only one is polymorphic: v5 pointed `metric_series` straight at `contents`, and `object_raw` was removed on 2026-08-07. `token_links` is the last polymorphic one. **The three satellites point polymorphically.** `token_links`, `metric_series`, and `object_raw` each carry `object` (which collection) + `object_id` (which row) instead of a typed foreign key. `erDiagram` cannot express a reference whose target collection is a runtime value, so those three edges are absent from the diagram above and described here instead: `object ∈ {contents, accounts, venues}` and `object_id` is that row's `_id`. One reference rule serves all three satellites, so adding a fourth object kind later does not change them. ### 1.2 Structural decisions | # | Decision | Consequence if violated | |---|---|---| | **D-1** | Schema fields are `snake_case`. Input contracts (`*Input`) are `camelCase`; conversion happens once, inside each model's `of()`. | Two naming conventions leak into the same file and reviewers stop trusting either. | | **D-2** | **Identity is `_id` (ObjectId).** A name ending in `_id` is always an ObjectId we issued, pointing at another row of ours. A name ending in `_key` is always an external string we did not mint. `tokens.address` is the one exception — it cannot be mistaken for an ObjectId. | A signature of `(id: string)` accepts both kinds and the wrong one **passes silently**. This is why every string business key (`content_key`, `account_key`, `venue_key`) is deleted. | | **D-3** | Platform codes are `x · tt · ig · yt · rd · gh · tg`, plus `web` provisionally (§1.11). One vocabulary for the `platform` field everywhere, including the denormalized copies on `token_links` and `metric_series`. | Cohort aggregation silently splits one platform into two buckets. | | **D-4** | Platform-supplied timestamps are prefixed: `account_created_at`, `venue_created_at`, `published_at`, `first_transfer_at`, `domain_created_at`. `created_at` / `updated_at` belong to `BaseModel` and mean *when we stored it*. | An unprefixed `created_at` collides with `BaseModel` and the account-age signal — the top rug indicator — is destroyed with no error. | | **D-5** ⚠️ | Polymorphic reference is `object` (enum of collection names) + `object_id` (ObjectId). Shared by `token_links`, `metric_series`, `object_raw`. **Only `token_links` still uses it** — `metric_series` became a direct 1:1 to `contents` in v5, and `object_raw` was removed on 2026-08-07. `$lookup.from` must be a constant, so every aggregation `$match`es on `object` first. | A satellite gains a per-kind field set and the "add a new object kind" cost multiplies by three. | | **D-6** | `mentions[]` elements are `{ platform_key, screen_name }` with `platform_key` **required**. Mentions are values, **not `_id` references**. | Handle-only mentions key the same KOL differently on the quote path and the mention path. Reference-ing them would create an empty account row per mention across 3,879 tweets. | | **D-7** | Field placement follows three questions: *(1) do we query on it?* → no: `data`. *(2) does it mean the same thing across every subtype?* → no: `data`. *(3) does the source of truth live elsewhere?* → yes: optimization copy, whose origin, refresh time, and usage limit must be stated. Otherwise: shared top-level field. Fill rate is **not** a criterion. | `views` becomes one field across TikTok autoplay, X impressions, and YouTube 30-second views, and every comparison built on it is contaminated. | | **D-8** | `source_urls[]` is the primary identity-resolution axis and holds **one entry per URL** (upsert of `status`/`observed_at`), never one entry per observation. At least one entry is required. Status *history* lives only in `token_links`. | Appending per observation grows the array without bound and duplicates the role of `token_links.status`, which is the single source of truth for the `ok → deleted` transition time. | **Row resolution order** (contract for the out-of-scope `ContentGenerator`, stated here because the indexes exist to serve it): 1. look up by `source_urls.url` · 2. else look up by `platform_key` · 3. else insert a new row. Step 1 comes first so that a platform which later changes its key format — Instagram shortcode → numeric pk — still resolves to the existing row. **Unresolved observations are still rows.** A URL we could not fetch becomes a `contents` row with `subtype = 'unknown'` and no `creator_id`. There is no "unlinked URL" holding pen; that is why `tokens.unclassified_links[]` is deleted. ### 1.3 `contents` — a coordinate in a platform's content space Holds tweets and videos, but also searches, shortlinks, and unclassified URLs. Splitting them would mean querying two collections to answer "what is getting attention on this platform". `unknown` means *"a platform we know, but we cannot tell where inside it"* — measured 398 cases, all `x.com` (282) and `tiktok.com` (97). | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | Identity. Satellites point here. | | `platform` | enum | ● | shared | D-3. | | `subtype` | enum | ● | shared | `tweet·video·photo·post·reel·tv·story·repo·gist·search·intent·shortlink·unknown` + `site` (provisional). | | `platform_key` | string | ○ | shared | The platform's own identifier. Response value if we fetched, else derived from the URL. **For `search` it is the query string.** | | `source_urls[]` | object[] | ● (≥1) | shared | `{ url, status, observed_at }`. D-8. | | `creator_id` | ObjectId | ○ | shared | → `accounts`. Absent for searches, unclassified URLs, and failed fetches. | | `venue_id` | ObjectId | ○ | shared | → `venues`. X community and Reddit subreddit only. | | `parent_content_id` | ObjectId | ○ | shared | Self-reference. Its reverse index replaces a separate edge collection. | | `parent_relation` | enum | ○ | shared | `quote·retweet·fork`. A property **of the referring side** — the original does not know it was quoted. | | `published_at` | Date | ○ | shared | Publication time. Never conflate with `created_at`. | | `observed_at` | Date | ● | shared | Regression guard. | | `text.primary` | string | ○ | shared | ig caption · yt/rd title · tt/x text · gh description. Search terms and hashtags go here too, which is what makes every subtype carry a value. | | `text.body` | string | ○ | shared | yt description · rd body. **The field exists but GitHub README is left empty for now** — it costs one extra call and the value of paying it is unmeasured. | | `text.from_media` | string | ○ | shared | ig `alt` · tt `transcriptionLink`. The only free entry point into visual content. | | `links[]` | string[] | default `[]` | shared | Outbound links in the body. Drainer / CA axis. | | `tags[]` | string[] | default `[]` | shared | Hashtags, GitHub topics. | | `mentions[]` | object[] | default `[]` | shared | `{ platform_key, screen_name }`. D-6. | | `author{}` | object | ○ | optimization | `{ account_id, handle, display_name, followers, account_created_at }`. Deliberate duplication of `accounts`: the master is overwritten, so "follower count at that moment" would otherwise be lost. | | `metrics_latest` | Mixed | ○ | optimization | SoT is `metric_series`. **First-pass filter only.** | | `link_stats{}` | object | ○ | optimization | `{ link_count, first_linked_at, computed_at, input_cutoff }`. **Must not be read without `computed_at`.** | | `data` | Mixed | default `{}` | per-platform | Platform-original key names, no namespace. `conversationId` · `musicId` · `pushedAt` · `moderation.*` · `productType`. | | `created_at` / `updated_at` | Date | ● | shared | `BaseModel`. | ### 1.4 `accounts` — who No `subtype`: knowing `platform` determines the kind (gh → owner, rd → user). | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | | | `platform` | enum | ● | shared | | | `platform_key` | string | ○ | shared | x `id` · gh `login`. **Reddit is the username string** — immutable and never reassigned, so the name is the id. | | `source_urls[]` | object[] | ● (≥1) | shared | D-8. | | `handles[]` | object[] | default `[]` | shared | `{ value, first_seen, last_seen }`. **The interval is the point** — TikTok rotates handles and reassigns old ones. | | `account_created_at` | Date | ○ | shared | Top rug signal. Five of six platforms supply it; Instagram structurally does not. | | `display_name` | string | ○ | shared | Effectively x and yt. | | `bio` | string | ○ | shared | Wallet addresses genuinely appear here — measured `"… BNB: 0x0Ae024D3C20…"`. | | `links[]` | string[] | default `[]` | shared | Profile outbound links. | | `declared_handles[]` | object[] | default `[]` | shared | `{ platform, value }`. Effectively gh `twitter_username`. | | `known_wallets[]` | string[] | default `[]` | shared | The on-chain ↔ social join axis. Derived from `bio`, so `bio` cannot substitute for it. | | `metrics.followers` | number | ○ | shared | Same meaning on every platform. Source of `contents.author.followers`. | | `moderation.unavailable` | boolean | ○ | shared | Suspended or deleted. "The account vanished after launch." | | `observed_at` | Date | ● | shared | Nested snapshots inherit the parent document's value. | | `data` | Mixed | default `{}` | per-platform | `following` · `totalKarma` · `heart` · `viewCount` · `stars` · `content_count` · `badges` · `account_type` · `source_ref`. | | `created_at` / `updated_at` | Date | ● | shared | | `account_type` (gh `User`/`Organization`) sits in `data`: there is no test record showing that organization-ness is a signal. `source_ref` (the marker that a value arrived embedded in a tweet response rather than from a direct profile fetch) sits in `data` too: it grades a value's trustworthiness but is not a query axis. `metrics.content_count` is in `data` because Reddit does not supply it and the counted unit differs per platform (x `statusesCount` includes retweets). ### 1.5 `venues` — where | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | | | `platform` | enum | ● | shared | `x` (community) · `rd` (subreddit) · `tg`. | | `subtype` | enum | ● | shared | `community·subreddit·channel·portal·shell·guard_group`. **Kept** because Telegram splits one URL three ways — portal 54%, empty shell 25%, active 4%. | | `platform_key` | string | ○ | shared | x community id · rd slug (immutable) · **tg `groupId`**. The channel name is mutable and therefore unfit as a join key (2 measured cases). | | `source_urls[]` | object[] | ● (≥1) | shared | D-8. | | `name` | string | ○ | shared | tg `title`. **Mutable** — take care when using it for as-of judgement. | | `description` | string | ○ | shared | Token CAs genuinely appear here. | | `venue_created_at` | Date | ○ | shared | "Created just before launch" signal. **Reddit has no collection path today.** | | `creator_id` | ObjectId | ○ | shared | → `accounts`. **No handle fallback** — response keys diverge between `userName` and `screen_name`. | | `metrics.members` | number | ○ | shared | tg `subscriberCount` · rd subscribers. | | `observed_at` | Date | ● | shared | | | `data` | Mixed | default `{}` | per-platform | `moderators` · `adminHandle`. | | `created_at` / `updated_at` | Date | ● | shared | | ### 1.6 `token_links` — token ↔ object, append-only **Never overwritten.** The *time* at which `status` moved `ok → deleted` is the signal, and only appending yields it. The repository exposes no `update`/`upsert` method at all, so the constraint holds at the type level rather than by convention. Since `contents.fetch_status` is deleted, **status history exists only here**. | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | | | `token_address` | string | ● | shared | Mint address. **Not an ObjectId** — tokens are never reclassified or merged, so a surrogate key has no work to do. | | `object` | enum | ● | shared | `contents·accounts·venues`. D-5. | | `object_id` | ObjectId | ● | shared | `{object_id, linked_at}` is the only path to "how many tokens referenced this object". | | `source_url` | string | ● | shared | **The reassignment safety net.** `object_id` may change; this does not — which is why no separate reassignment-history collection is needed. | | `linked_at` | Date | ● | shared | The as-of cutting axis. Overwriting makes the ordinal unrecoverable. | | `observed_at` | Date | ● | shared | | | `status` | enum | ● | shared | `ok·deleted·unavailable·unresolved`. SoT for status history. | | `name_match` | boolean | ○ | shared | Does the tg channel name contain the token name. **`title` is mutable, so this cannot be recomputed later** — recorded at observation time. | | `platform` | enum | ● | optimization | Copied from the target. Per-platform exposure aggregation resolves in **one collection scan**. | | `subtype` | enum | ○ | optimization | Copied from the target. "TikTok search vs video" ends here. **Optional, not required** — `accounts` has no `subtype` (§1.4), so a link pointing at an account has nothing to copy. Requiring it would make account links unstorable. | | `first_transfer_at` | Date | ○ | optimization | Δt with zero joins. | | `lineage[]` | ObjectId[] | default `[]` | optimization | Self + ancestors, length capped at 2. Self sits at index 0 so the query needs no `$or`. | | `link_depth` | number | ● | optimization | `lineage.length − 1`. **Never accepted as a parameter** — if the two disagree the query returns a wrong number without erroring. | | `created_at` / `updated_at` | Date | ● | shared | | ### 1.7 `metric_series` — metric time series, one row per target Points accumulate in one row's array rather than as one row per observation. Velocity is then a single read and the array is already ordered. A point is ≈150 B, so the design cap of 200 points is ≈30 KB. | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | | | `object` | enum | ● | shared | D-5. **Polymorphic, confirmed** — one collection covers content metrics, account follower counts, and venue member counts. B-3 (leading detection of a follower spike by Z-score) requires an account time series, and the one-row-per-target shape makes covering all three free. | | `object_id` | ObjectId | ● | shared | Unique together with `object` — one row per target. | | `platform` | enum | ● | optimization | Copied from the target. Cohort filter. | | `points[]` | object[] | default `[]` | shared | `$push` per observation, **`$slice: -200`**. The cap is enforced by the repository, not by a comment. | | `points[].at` | Date | ● | shared | **Observation time.** rd and tt use the actor's `crawledAt` — using storage time mixes batch lag into velocity. | | `points[].metrics` | Mixed | ● | per-platform | Platform-original key names. `METRIC_SPEC` whitelists keys **at write time**. | | `created_at` / `updated_at` | Date | ● | shared | | **Accepted cost.** A platform-cohort percentile cannot be produced by an index range scan (`$unwind` is required). Percentiles belong to the derived layer (`metric_ranks`), computed periodically, which is out of scope. ### 1.8 ~~`object_raw` — lossless originals, 1:1~~ ⚠️ REMOVED 2026-08-07 > **The section title states the claim that killed it.** "Lossless originals" was never true: the > payload had already passed through the SDK's transform before anything above the SDK could see > it. The collection, its model and repository, and the `raw` field that fed it were all deleted > while the data was still empty — which is the cheapest moment to do it. > > Reviving it cannot reuse the identity axis defined below. `(object, object_id)` requires a saved > row, but the response exists **before** the row is saved. The replacement direction is to keep > the original inside the SDK layer, where it is actually visible > ([`../social-generator/decisions.md`](../../../guides/social-recording/decisions.md) G-12). > > Everything below is kept as written, for the record. Renamed from `content_raw`: the collection holds contents, accounts, and venues, so the old name contradicted its contents. `data` is renamed `payload` because it collided with `contents.data`. | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | | | `object` | enum | ● | shared | D-5. | | `object_id` | ObjectId | ● | shared | Unique. | | `payload` | Mixed | ● | per-platform | The entire response. **Preserving the original is what makes reinterpretation possible** — the audit missed `alt`, `isSlideshow`, and `author.id` precisely because the stored dump had been trimmed. | | `observed_at` | Date | ● | shared | Upsert, so the latest one only. | | `created_at` / `updated_at` | Date | ● | shared | | No `platform` field: this collection is only ever read by key, so a filter field would earn nothing and would widen a document that is already tens of KB. ### 1.9 `tokens` — thin state | Field | Type | Required | Kind | Notes | |---|---|---|---|---| | `_id` | ObjectId | ● | shared | References use `address`, not this. | | `address` | string | ● | shared | Mint address, unique. Join target of `token_links.token_address`. | | `symbol` | string | ○ | optimization | SoT is the tracker. Display, and input to `name_match`. | | `first_transfer_at` | Date | ○ | optimization | Age anchor. | | `discovered_at` | Date | ● | shared | Entry point for collection and backfill batches. | | `fingerprints[]` | string[] | default `[]` | shared | **Unchanged until the web work lands.** `domain:X` is the axis of the domain-sharing count. | | `web{}` | object | ○ | shared | **Unchanged until the web work lands** (17 fields, §1.11). | | `created_at` / `updated_at` | Date | ● | shared | | No `price`, `ath_price`, or `alpha_score`: they change continuously, so a copy is stale the moment it is read and it contaminates backtests. ### 1.10 Indexes 23 indexes. Field order is significant — Equality, Sort, Range. | Collection | Name | Key | Options | Question it answers | |---|---|---|---|---| | `contents` | `uniq_contents_source_url` | `{ 'source_urls.url': 1 }` | unique | Row resolution step 1; prevents one URL splitting into two rows | | | `idx_contents_platform_subtype` | `{ platform: 1, subtype: 1 }` | | "What is getting attention on this platform" | | | `idx_contents_platform_key` | `{ platform: 1, subtype: 1, platform_key: 1 }` | partial `platform_key` exists | Row resolution step 2 | | | `idx_contents_creator_published` | `{ creator_id: 1, published_at: -1 }` | partial `creator_id` exists | "This KOL's latest content" — serial-shill detection | | | `idx_contents_parent` | `{ parent_content_id: 1 }` | partial exists | Lineage traversal; mostly null | | | `idx_contents_venue` | `{ venue_id: 1 }` | partial exists | Content per community; only two platforms populate it | | `accounts` | `uniq_accounts_source_url` | `{ 'source_urls.url': 1 }` | unique | Row resolution step 1 | | | `uniq_accounts_platform_key` | `{ platform: 1, platform_key: 1 }` | unique, partial `platform_key` exists | Target identity | | | `idx_accounts_handle` | `{ 'handles.value': 1 }` | | Handle → account. **Always resolve as-of with the interval** | | | `idx_accounts_wallet` | `{ known_wallets: 1 }` | | Wallet → account. Partial impossible — default `[]` means it always exists | | `venues` | `uniq_venues_source_url` | `{ 'source_urls.url': 1 }` | unique | Row resolution step 1 | | | `idx_venues_platform_key` | `{ platform: 1, subtype: 1, platform_key: 1 }` | partial `platform_key` exists | Target identity | | | `idx_venues_creator` | `{ creator_id: 1 }` | partial exists | "Communities this account keeps opening" | | `token_links` | `idx_links_object_linked` | `{ object_id: 1, linked_at: 1 }` | | **Primary path** — "tokens on this object", as-of | | | `idx_links_token_linked` | `{ token_address: 1, linked_at: 1 }` | | "Every social for this token" | | | `idx_links_lineage_linked` | `{ lineage: 1, linked_at: 1 }` | multikey | "Effective reuse count". `lineage` is the only array, so the compound index is legal | | | `idx_links_platform_linked` | `{ platform: 1, subtype: 1, linked_at: 1 }` | | Per-platform exposure; zero joins thanks to the copies | | `metric_series` | `uniq_series_object` | `{ object: 1, object_id: 1 }` | unique | Guarantees one row per target. `object` leads because the reference is polymorphic | | | `idx_series_platform` | `{ platform: 1 }` | | Cohort filter | | ~~`object_raw`~~ | ~~`uniq_raw_object`~~ | ~~`{ object: 1, object_id: 1 }`~~ | — | ⚠️ **Removed 2026-08-07.** This axis is also why it cannot simply be revived: at fetch time the row is not saved yet, so there is no `object_id` to key on | | `tokens` | `uniq_tokens_address` | `{ address: 1 }` | unique | Join target | | | `idx_tokens_fingerprints` | `{ fingerprints: 1 }` | multikey | Domain-sharing count. Partial impossible — default `[]` | | | `idx_tokens_discovered` | `{ discovered_at: -1 }` | | Recently ingested tokens | **Why `platform_key` indexes are partial.** `platform_key` is optional — Instagram cannot supply one before payment. A plain unique index treats every missing value as `null`, so the *second* Instagram account without a key would be rejected with a duplicate-key error. `partialFilterExpression: { platform_key: { $exists: true } }` is what makes the uniqueness constraint mean "unique among the rows that have one". **Why the satellite lookups take `object` as well as `object_id`.** `uniq_series_object` and `uniq_raw_object` are compound with `object` in the leading position, so a query on `object_id` alone cannot use the index prefix and degrades to a collection scan. Every repository method on `MetricSeries` and `ObjectRaw` therefore accepts the pair. (⚠️ `ObjectRaw` no longer exists as of 2026-08-07; `MetricSeries` keys on `content_id` alone since v5.) `token_links` is the exception — its primary index is `{ object_id, linked_at }`, so `countByObjectAsOf` takes `object_id` alone. **Why `source_urls[]` requires at least one entry.** An empty array indexes as a single `undefined` entry, so two documents with empty arrays collide on the unique index. The `of()` factory rejects an empty array. **Why `partialFilterExpression` cannot express "non-empty array".** It supports neither `$ne` nor `$size`, and a field declared `required` with `default: []` always satisfies `$exists`. That is why `known_wallets` and `fingerprints` carry plain multikey indexes. ### 1.11 Provisional and deferred **Web is deferred to separate work.** Interim rules, in force until then: 1. Web URLs go into `contents` with `platform = 'web'`, `subtype = 'site'`. 2. `tokens.web{}` and `tokens.fingerprints[]` stay exactly as they are. 3. No domain-layer fields are added to `contents(web)`, and there is no `site_id`. Rule 2 is the load-bearing one: the domain-sharing count keeps working through its existing path during the interim, so validated signals (`knowyourmeme.com` 22 cases 100% rug · `axiom.trade` 21 at 90% · `orynth.dev` 18 at 0%) do not break. The ordering is safe because "attribute it to the token" as the default makes the later split purely additive — create `sites`, backfill `site_id` onto `contents(web)` rows, and **the links are never touched**. The reverse order is destructive. **What the previous schema had and this one does not** | Removed | Why | |---|---| | `content_key` · `account_key` · `venue_key` | Identity moved to `_id` (D-2). A string key could not survive reclassification. | | `contents.source_type` | Decomposed into `platform` + `subtype`. | | `contents.source_key` · `contents.form` | Replaced by `source_urls[]` and `subtype`. | | `contents.fetch_status` | Duplicated `source_urls[].status`; history belongs to `token_links`. | | `ext` (3 collections) | Renamed `data`, namespace dropped — `platform` is already a top-level field, so a prefix repeated it. | | `accounts.subtype` | `platform` determines the kind. | | `accounts.moderation.unavailable_reason` | Not a query axis, and `unavailable` alone carries the signal. | | `accounts.account_type` · `accounts.source_ref{}` (as top-level fields) | Moved into `data` — see §1.4. | | `venues.channel_state` | `subtype` covers it. | | `venues.data.nameMatch` | Moved to `token_links.name_match`. | | `token_links.content_key` | The cause of the link-loss problem. | | `token_links.source_field` | Publishers do not use the fields as labelled — the `website` slot held Reddit 18, GitHub 15, TikTok-search 6. | | `tokens.trigger_reason` | Not a query axis. | | `tokens.unclassified_links[]` | Every URL is now an object, so the role is gone. **This field's existence was the symptom of having nowhere to put a non-content.** | | `tokens.price` · `ath_price` · `alpha_score` | Owned by the tracker; a copy is stale on read. | --- ## 2. API Specification **Not applicable.** The draft excludes controllers, so this iteration serves no endpoints. When services and controllers are designed in a later session, the API section is authored then. --- ## 3. Service & Class Design (Model + Repository only) ### 3.1 Class structure ```mermaid flowchart LR Gen["ContentGenerator
«out of scope»"] Reader["QueryConsumer
«out of scope»"] Content["Content
«Model»"]:::added Account["Account
«Model»"]:::added Venue["Venue
«Model»"]:::added Token["Token
«Model»"]:::added Link["TokenLink
«Model»"]:::added Series["MetricSeries
«Model»"]:::added Raw["ObjectRaw ⚠️ deleted 2026-08-07
«Model»"]:::added ContentRepo["ContentRepository"]:::added AccountRepo["AccountRepository"]:::added VenueRepo["VenueRepository"]:::added TokenRepo["TokenRepository"]:::added LinkRepo["TokenLinkRepository"]:::added SeriesRepo["MetricSeriesRepository"]:::added RawRepo["ObjectRawRepository ⚠️ deleted 2026-08-07"]:::added DomainModule["SocialGraphDomainModule
«Module»"]:::added Gen -->|uses| Content Gen -->|uses| Account Gen -->|uses| Venue Gen -->|uses| Token Gen -->|uses| Link Gen -->|uses| Series Gen -->|uses| Raw Gen -->|uses| ContentRepo Gen -->|uses| AccountRepo Gen -->|uses| VenueRepo Gen -->|uses| TokenRepo Gen -->|uses| LinkRepo Gen -->|uses| SeriesRepo Gen -->|uses| RawRepo Reader -->|uses| LinkRepo Reader -->|uses| SeriesRepo Reader -->|uses| AccountRepo DomainModule -->|uses| ContentRepo DomainModule -->|uses| AccountRepo DomainModule -->|uses| VenueRepo DomainModule -->|uses| TokenRepo DomainModule -->|uses| LinkRepo DomainModule -->|uses| SeriesRepo DomainModule -->|uses| RawRepo classDef added fill:#d4edda,stroke:#28a745,stroke-width:2px ``` Dependency direction is one-way (`Module → Repository → Mongoose model`); models depend on nothing. No cycles. ### 3.2 File layout ``` src/modules/social-graph/ ├── content.model.ts Content + ContentEntity / ContentModel ├── content.repository.ts ContentRepository ├── account.model.ts Account ├── account.repository.ts AccountRepository ├── venue.model.ts Venue ├── venue.repository.ts VenueRepository ├── token.model.ts Token ├── token.repository.ts TokenRepository ├── token-link.model.ts TokenLink ← was content-token-link ├── token-link.repository.ts TokenLinkRepository ├── metric-series.model.ts MetricSeries ← was content-metric-snapshot ├── metric-series.repository.ts MetricSeriesRepository ├── object-raw.model.ts ObjectRaw ← ⚠️ deleted 2026-08-07 ├── object-raw.repository.ts ObjectRawRepository ← ⚠️ deleted 2026-08-07 ├── metric-spec.ts METRIC_SPEC + pickKnownMetrics (unchanged) ├── social-graph.consts.ts collection names, enums, LINEAGE_MAX_DEPTH, SERIES_POINT_CAP ├── social-graph.types.ts *Input contracts (camelCase) └── social-graph.domain.module.ts forFeatureAsync ×7 + providers/exports ``` The seven collections are written together as one cohesive unit, so they share a **single domain module**. Seven separate modules would always be imported together. ### 3.3 Key members | Class | Key methods | |---|---| | `Content` | `static of(input: ContentInput): Content` · `isAlive(): boolean` · `addSourceUrl(url, status, observedAt): void` · `updateMetricsLatest(metrics, observedAt): void` · `updateLinkStats(linkCount, firstLinkedAt, inputCutoff): void` | | `Account` | `static of(input: AccountInput): Account` · `addSourceUrl(...)` · `addHandleObservation(value, observedAt): void` · `hadHandleAt(handle, at): boolean` · `currentHandle(): string \| null` · `isObservedLaterThan(other): boolean` | | `Venue` | `static of(input: VenueInput): Venue` · `addSourceUrl(...)` | | `Token` | `static of(input: TokenInput): Token` · `addFingerprints(values): void` · `updateWeb(web): void` | | `TokenLink` | `static of(input: TokenLinkInput): TokenLink` · `static buildLineage(objectId, ancestorIds): Types.ObjectId[]` · `isDirect(): boolean` | | `MetricSeries` | `static of(object, objectId, platform): MetricSeries` · `static buildPoint(platform, metrics, observedAt): MetricPoint` — `object` is any of the three kinds, not `contents` only | | ~~`ObjectRaw`~~ | ~~`static of(object, objectId, payload, observedAt): ObjectRaw`~~ ⚠️ class deleted 2026-08-07 | | `ContentRepository` | `create(content)` · `save(entity)` · `findById(id)` · `findByIds(ids)` · `findBySourceUrl(url)` · `findByPlatformKey(platform, subtype, platformKey)` · `findByCreator(creatorId, limit)` · `findChildren(parentContentId)` · `findByVenue(venueId, limit)` | | `AccountRepository` | `create` · `save` · `findById` · `findByIds` · `findBySourceUrl(url)` · `findByPlatformKey(platform, platformKey)` · `findByHandle(handle): Promise` · `findByWallet(wallet): Promise` | | `VenueRepository` | `create` · `save` · `findById` · `findBySourceUrl(url)` · `findByPlatformKey(platform, subtype, platformKey)` · `findByCreator(creatorId)` | | `TokenRepository` | `upsertByAddress(token)` · `findByAddress(address)` · `findByFingerprint(fingerprint)` · `findRecentlyDiscovered(limit)` | | `TokenLinkRepository` | `appendMany(links)` · `countByObjectAsOf(objectId, asOf)` · `countByLineageAsOf(lineageId, asOf)` · `findByLineageAsOf(lineageId, asOf)` · `findByToken(tokenAddress, asOf)` · `findLatestByObjectAndToken(objectId, tokenAddress)` — **no `update`, no `upsert`** | | `MetricSeriesRepository` | `appendPoint(object, objectId, platform, point)` · `findByObject(object, objectId)` · `findLatestPoint(object, objectId)` · `findPlatformCohort(platform, limit)` | | ~~`ObjectRawRepository`~~ | ~~`upsertByObject(raw)` · `findByObject(object, objectId)`~~ ⚠️ class deleted 2026-08-07 | ### 3.4 Behaviour notes **Rich models, not object literals.** Per `guides/project-mistakes.md`, entities are never built from partial object literals — every model exposes a `static of()` factory. A missing field in a literal compiles cleanly; a factory makes the omission explicit. typegoose models must not use `private`; use a `_` prefix with `public`. **No `buildKey` anywhere.** Identity is `_id`, so there is no key to assemble. The methods that vanished with it (`Content.buildKey`, `Account.buildKey`, `Venue.buildKey`) have no replacement — the caller resolves an existing row through `findBySourceUrl` / `findByPlatformKey` and otherwise creates one. **Row resolution is the caller's, and the race is intentional.** No repository offers "find-or-create". Two concurrent creates for the same URL end with the second rejected by `uniq_*_source_url`; the caller re-runs step 1 and finds the winner. Making the repository hide this would mean an array-targeted upsert, which cannot express "add this URL only if the row does not already have it" atomically. **`Content.addSourceUrl`.** If the URL is already present, updates that entry's `status` and `observed_at`; otherwise pushes a new entry (D-8). The array therefore holds one entry per distinct URL, never one per observation. **`Account.addHandleObservation`.** Compares against the **last** entry only. If its `value` matches, `last_seen` extends to `max(existing, observedAt)`. If it differs, a new entry is pushed and the previous entry keeps the `last_seen` it already had — it is *not* stretched forward to the changeover time. The span between the last confirmed sighting of A and the first sighting of B is therefore a **gap that belongs to nobody**, and `hadHandleAt` returns false inside it. That is deliberate: we never observed who held the handle during the gap, and claiming the previous owner held it would be asserting an unobserved fact. The one case where the previous entry is modified is an out-of-order arrival — if its `last_seen` somehow sits after `observedAt`, it is truncated back to `observedAt` so the two intervals cannot overlap. An A→B→A rotation therefore produces **three intervals**, not two — a handle that was reassigned away and later reacquired is two distinct periods of ownership, and merging them would let an observation from the gap resolve to the wrong account. **`Account.hadHandleAt`.** True when an entry exists with `value === handle` and `first_seen ≤ at ≤ last_seen`. This is the mandatory follow-up whenever `findByHandle` returns more than one account. **`TokenLink.buildLineage`.** `[objectId, ...ancestorIds]`, deduplicated by string form, truncated to `LINEAGE_MAX_DEPTH`. `link_depth` is derived as `lineage.length - 1` and never accepted as a parameter — passing it separately allows the two to disagree, and then the effective-reuse query returns a **wrong number without erroring**. Deduplication compares `ObjectId.toHexString()`, because two distinct `ObjectId` objects holding the same value are not `===`. **`MetricSeriesRepository.appendPoint` is an upsert with a cap.** One `findOneAndUpdate` filtered on `{ object, object_id }` — the full unique key — with `$push: { points: { $each: [point], $slice: -SERIES_POINT_CAP } }`, `$setOnInsert` for `platform`, and `upsert: true`. The cap lives in the query, not in a comment, so it holds no matter which caller writes. Filtering on `object_id` alone would miss the index prefix and race against the unique constraint on insert. **`Content.updateMetricsLatest` advances `observed_at`.** There is no metrics-only watermark field, so the document's `observed_at` doubles as the regression guard and moves forward on every accepted metrics update. A metrics update whose `observedAt` predates the stored value is ignored. Acceptable while the derived caches are first-pass-filter-only (§4 item 2). **Repositories that cannot overwrite.** `TokenLinkRepository` exposes no `update`/`upsert` method at all. Overwriting a link destroys the as-of ordinal. **Skipping the metric append is the biggest trap.** Masters may be left alone on re-observation ("it already exists, just add the link"), but metrics must be re-recorded — the engagement growth between two tokens referencing the same content *is* the velocity signal. **No `ClientSession` parameter.** Phase 1.6 excluded transaction handling, so repository writes are independent. The writes of one ingest are therefore **not atomic**. See §6. **`TokenRepository` deliberately offers no price lookup.** Providing one would legitimise a stale local copy. It also offers no `findByDomain`: `fingerprints` carries `domain:X`, so a second domain index would be redundant. ### 3.5 Input contracts `social-graph.types.ts`, all `camelCase` (D-1): | Interface | Notable fields | |---|---| | `SourceUrlInput` | `url` · `status: FetchStatus` · `observedAt` | | `ContentInput` | `platform` · `subtype` · `platformKey?` · `sourceUrls: SourceUrlInput[]` (≥1) · `creatorId?` · `venueId?` · `parentContentId?` · `parentRelation?` · `publishedAt?` · `text` · `links` · `tags` · `mentions: MentionInput[]` · `author?` · `data?` · `observedAt` | | `MentionInput` | `platformKey` (**required**) · `screenName?` | | `AccountInput` | `platform` · `platformKey?` · `sourceUrls` · `handle?` · `accountCreatedAt?` · `displayName?` · `bio?` · `links?` · `declaredHandles?` · `knownWallets?` · `metrics?` · `moderation?` · `data?` · `observedAt` | | `VenueInput` | `platform` · `subtype` · `platformKey?` · `sourceUrls` · `name?` · `description?` · `venueCreatedAt?` · `creatorId?` · `metrics?` · `data?` · `observedAt` | | `TokenInput` | `address` · `symbol?` · `firstTransferAt?` · `discoveredAt` · `fingerprints?` · `web?` | | `TokenLinkInput` | `tokenAddress` · `object` · `objectId` · `sourceUrl` · `linkedAt` · `status` · `nameMatch?` · `platform` · `subtype?` · `firstTransferAt?` · `ancestorIds?` · `observedAt` | `link_depth` is absent from `TokenLinkInput` by design, and so is `lineage` — both are derived inside `of()`. ### 3.6 Data source map | Data | Candidate sources | Chosen | Cost | |---|---|---|---| | Current engagement metrics | `contents.metrics_latest` (cache) · `metric_series` last point (SoT) | **Cache** for list queries; SoT for timelines and precise judgement | Stale until the next observation; nothing is lost since the original stays in the series | | Link liveness | `token_links.status` (history SoT) | **SoT only** — the `contents.fetch_status` cache is deleted | An extra read per liveness question, in exchange for keeping the `ok→deleted` transition time | | Content reuse count | `contents.link_stats.link_count` (derived scalar) · `token_links` count with `linked_at ≤ T` | **Count** for precise judgement; scalar as a first-pass filter | Recompute interval undecided, so the scalar can lag | | ~~Raw response~~ | ~~`object_raw.payload`~~ | ⚠️ **No store as of 2026-08-07** — whatever the generator does not map into `contents`/`accounts`/`venues` is gone, and recovering it means fetching again | — | | Token price / ATH / `alpha_score` | `sol-alpha-finder-tracker` HTTP call | Single source — copying is forbidden | No repository in this scope | --- ## 4. External Validation | # | Decision | Verdict | Alternative + trade-off | |---|---|---|---| | 1 | Time-series collection not adopted | **Strongly recommended** | Adopting it — compression benefit is negligible at this scale, the lazy-registration footgun is real, and time-series collections cannot be written inside a transaction (still true in 8.0). `metric_series` reaches the same goal with an ordinary collection plus a capped array. | | 2 | Derived caches (`metrics_latest`, `link_stats`) | **Conditional** | No cache (always join) — join cost vs. **stale-freeze risk**. `guides/project-mistakes.md` records a real incident of exactly this shape. **Conditions:** (a) `link_stats` must not be read without `computed_at`/`input_cutoff` — `updateLinkStats()` always sets both; (b) both are first-pass filters only; (c) when the re-observation trigger is decided, design their refresh path **together with it**. Note that v4 removed the third cache (`fetch_status`), shrinking this exposure. | | 3 | Edge collection instead of array embedding | **Strongly recommended** | Array — without `$elemMatch` MongoDB cannot intersect multikey index bounds, so the as-of cut stops being index-bounded. | | 4 | Self-inclusive `lineage` + `{lineage, linked_at}` | **Strongly recommended** | `parents` + `$or` — per-branch scans plus dedup, and one missing branch index drops the query to a collection scan. A compound index may hold at most one array field, and `linked_at` sits outside the array, so both bounds hold. | | 5 | D-2 `_id` identity | **Strongly recommended (changed from v3)** | String business keys — v3's choice. It cannot survive reclassification: when a URL's identity is later resolved, every edge keyed on the old string breaks. Surrogate keys are the standard answer and cost one indexed lookup. | | 6 | `metrics: Mixed` polymorphic | **Conditional** | Unified common axis (retracted for semantic contamination). **Condition:** `METRIC_SPEC` owns **write-time key whitelist validation** — `Mixed` performs no schema validation, so a typo key (`playCont`) is stored silently. Already implemented. | | 7 | Polymorphic `object` + `object_id` without a DB-level foreign key | **Conditional** | Three typed collections per satellite — 3× the schema for one reference rule. **Condition:** referential integrity is unenforced by MongoDB, so a deleted object leaves dangling satellites. Acceptable because nothing in this design deletes objects. | **Sources:** [Time Series Collection Limitations](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations/) · [Transactions](https://www.mongodb.com/docs/manual/core/transactions/) · [Multikey Index Bounds](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds/) · [Partial Indexes](https://www.mongodb.com/docs/manual/core/index-partial/) --- ## 5. Implementation Checklist - [ ] `SocialPlatform` (incl. `web`), `ContentSubtype`, `VenueSubtype`, `ObjectKind`, `LinkStatus`, `FetchStatus`, `ParentRelation` defined as TS `enum`s and passed to `@prop({ enum: E })` - [ ] All seven models extend `BaseModel` and implement `static generateSchema('')` - [ ] Every model exposes `static of()`; no repository accepts a partial object literal for creation - [ ] No `private` members on typegoose models (`_` prefix + `public` instead) - [ ] No `*_key` string identity field survives; every intra-DB reference is an `ObjectId` named `*_id` (D-2) - [ ] `of()` rejects an empty `sourceUrls` array (§1.10) - [ ] Indexes declared as `@index({...})` class decorators exactly as listed in §1.10, including `partialFilterExpression` on every `platform_key` index - [ ] `TokenLinkRepository` exposes no `update`/`upsert` - [ ] `MetricSeriesRepository.appendPoint` uses `$slice: -SERIES_POINT_CAP` - [ ] `AccountRepository.findByHandle` returns an array (never a single entity) - [ ] `social-graph.domain.module.ts` registers all seven via `forFeatureAsync(..., DB_CONNECTION)` and exports the repositories - [ ] **Prerequisite:** `social-fetcher.router.normalize()` actually normalizes the returned `url` (§6) - [ ] **Prerequisite:** `reddit.fetcher.classify()` routes `/r/{sub}/search?q=` to `contents` with `subtype: search`, not to a venue (§6) - [ ] Integration tests assert index name, key, **key order**, uniqueness, and `partialFilterExpression` for all 23 indexes --- ## 6. Out of Scope (Explicit Exclusions) Signal was detected for both categories below during Phase 1.6 and the user explicitly opted out. Recorded as an audit trail — "why we did not do X" — traceable during code review or an incident post-mortem. | Aspect | Why excluded | Consequence in this design | Revisit trigger | |---|---|---|---| | **Failure handling** (transaction boundary, idempotency keys) | Phase 1.6: user declared explicit exclusion after signal detection. | Repositories take no `ClientSession`, so the writes of a single ingest are **not atomic** — a mid-way crash leaves masters written and logs missing. No idempotency key on `token_links`, so duplicate appends are possible and deduplication is the query side's responsibility. **Reassignment is also non-atomic**: a crash mid-reassignment leaves some links repointed and some not; queries still resolve because every link points at a valid object, and re-running the reclassification batch converges. | The first observed partial-write inconsistency in production, or when a re-observation trigger starts producing repeated appends of the same `(object, token, linked_at)`. | | **Data lifecycle** (TTL, retention, archival) | Phase 1.6: user declared explicit exclusion after signal detection. | No TTL index anywhere. `token_links` grows without bound. (`object_raw` held tens-of-KB documents; removing it on 2026-08-07 took that pressure away.) `metric_series` is the exception — `$slice: -200` bounds it structurally. | When storage cost becomes a real constraint, or when per-platform retention must diverge. | **Also out of scope, tracked elsewhere** | Item | Where it belongs | |---|---| | `ContentGenerator` — the adapter that fills these objects | Next iteration. Until it exists, "can this field actually be populated" is unverified for `source_urls[]`, `parent_relation`, and `name_match`. | | Web / domain layer (`sites`, RDAP fingerprints, `site_id`) | Separate work. Interim rules in §1.11. | | Derived layer (`metric_ranks`, entity resolution, fingerprint clustering) | Separate work. | | `social-fetcher` fixes — see the table below | `social-fetcher` module. Two of them **block this schema**. | | GitHub README into `text.body` | The field exists; only the value is left empty. Costs one extra call per repo and the value of paying it is unmeasured. | **`social-fetcher` prerequisites, split by whether they block v4** | Location | When | What | |---|---|---| | `router.normalize()` | **Blocking** | The returned `url` is `u.toString()` — the `www.` stripping, tracking-parameter cleanup, and trailing cleanup the comment promises are applied to a local `host` variable only. `source_urls.url` is a unique index axis and the primary row-resolution key, so `www.foo.com/promo` and `foo.com/promo` become two rows for one target. | | `reddit.fetcher.classify()` | **Blocking — newly created by v4** | `/r/{sub}/search?q=` is still classified as a subreddit venue. v4 introduces `subtype: search` on `contents`, so it must now become a `contents(search)` row keyed by the query. Today `?q=wendys` and `?q=wendys+dumpster` fold into the same venue row. | | fetcher `mentions[]` as `{id, screenName}` | Blocking | **Already satisfied** — verified during the previous iteration. | | `METRIC_SPEC` write-time whitelist | Blocking | **Already satisfied** — `pickKnownMetrics` implemented in `metric-spec.ts`. | | `router.apexDomain()` | Web work | Reads only the last two labels, so `shop.foo.co.uk` → `co.uk`. Needs the Public Suffix List. Only matters once the domain-sharing count moves to the web layer. | | `reddit.fetcher` catch-all | Separate | The code comment warns about itself: `reddit.com/settings` becomes a fake subreddit key, and **Reddit has the highest unit cost, and this path mints a *subreddit* key, so that one call is $0.04** (not the $0.022 a post URL costs). Whether to keep a reserved-word list is undecided; there is no observation count to decide on. | **Already fixed upstream — recorded so the audit sample is not misread.** Two classifier mis-folds found in the audit sample are fixed in the current code: Instagram `/stories/{handle}/{storyId}` now keys on `storyId`, and Reddit `/r/{sub}/s/{shareId}` is now its own `reddit_share` type. `samples/` is a snapshot of behaviour at observation time, not of current behaviour — a claim about code must be checked against code.