Clarify canonical source attribution: `thoughts.source_type` vs `thoughts.metadata.source`

[thewoolleyman]

What's on your mind?

Summary

There appears to be schema drift around how OB1 represents the source of a thought.

Observed patterns include:

• Stock/core write paths and source filtering guidance use thoughts.metadata.source, queried in SQL as thoughts.metadata->>'source'.
• Newer dashboard, schema, integration, and stats contributions read or write a dedicated thoughts.source_type column.
• Some current contributions write both fields, or perform a follow-up update to set source_type after calling stock upsert_thought, because stock upsert_thought only persists p_payload->'metadata'.
• At least one prior recipe discussion exposed confusion around whether source belongs as a top-level thoughts column versus inside thoughts.metadata.

This makes it unclear which representation new recipes, dashboards, migrations, and user-contributed integrations should target.

(This issue was AI-generated with OpenAI Codex)

Why This Matters

If one component writes only thoughts.metadata.source while another reads only thoughts.source_type, source filtering, dashboard grouping, statistics, and backfill workflows can silently miss records.

If thoughts.source_type is intended to be an enhanced-schema projection or denormalized index-friendly representation of thoughts.metadata.source, that should be documented explicitly. If it is intended to replace thoughts.metadata.source, then recipes and existing source-filtering guidance likely need migration guidance.

This is especially important because the current stock setup guide's upsert_thought(p_content, p_payload) stores only p_payload->'metadata'. A caller that sends sibling top-level keys such as source_type can succeed while silently leaving enhanced columns unset unless it performs a second update or mirrors the value into metadata.

Current Observed Patterns

• Source filtering work documents a source parameter for search_thoughts, list_thoughts, and thought_stats:
  [recipes] Source filtering for Open Brain thoughts #30

• Slack deduplication recipe discussion uses thoughts.metadata for source-specific event identity, and also surfaced confusion about top-level source columns versus JSONB metadata:
  [recipes] Slack message deduplication pattern for thought ingestion #89

• The dashboard contribution was merged under dashboards/open-brain-dashboard-next/, and appears relevant to source/type filtering behavior:
  [dashboards] Full-featured web dashboard for thought management #111

• Enhanced schema work adds thoughts.source_type as an idempotent structured column on thoughts:
  [schemas] Enhanced thoughts columns and utility RPCs #191

• Brain health monitoring views read source_type for source volume and enrichment gap monitoring:
  [recipes] Brain health monitoring views #194

• Consolidation worker work treats source_type as part of prepared payloads and notes that stock upsert_thought can drop sibling top-level fields like type, importance, and source_type on first-run inserts:
  [integrations] Consolidation workers (bio + metadata) #200

• Synthesis capture work mirrors provenance into metadata because stock upsert_thought drops top-level payload keys; its anti-loop checks read top-level source_type when available:
  [recipes] Synthesis capture — Query-as-Ingest pattern #212

• Brain stats daily and heatmap work depends on source_type from the enhanced-thoughts schema:
  [schemas] Brain stats daily + heatmap filter #221

• Wiki pipeline work includes a fallback from source_type to metadata-derived source fields when the column is not present:
  [recipes] Wiki pipeline: OpenRouter + thought_edges + prompt safety #234

• Readwise capture/import work writes both source_type = 'readwise' and metadata.source = 'readwise', and documents that some RPCs filter on source_type:
  [integrations] Add Readwise highlight capture (backfill + webhook + schema) #237

• The open-brain-rest gateway writes through stock upsert_thought with metadata.source, then separately updates top-level source_type for dashboard reads:
  [integrations] open-brain-rest — Cloudflare Worker REST gateway for the Next.js dashboard #239

Related but Not Duplicate

• Standardizing ingestion patterns is already tracked separately:
  [architecture] Standardize thought ingestion patterns across recipes and integrations #61

  That issue is broader. This issue is specifically about canonical source attribution and read/write compatibility between metadata.source and source_type.

• A prior closed issue reported similar drift for type: a migration assumed a top-level thoughts.type column on a stock schema where type lived in metadata->>'type':
  [schemas] workflow-status migration fails: type column does not exist on thoughts table #182

  The fix changed the workflow-status backfill to read metadata->>'type' instead:
  [repo] Sweep fix-now backlog issues #185

  That prior fix does not resolve source attribution, but it shows the same class of confusion around stock metadata fields versus enhanced top-level columns.

Questions for Maintainers

1. What is the canonical source attribution field for OB1 going forward: thoughts.metadata.source, thoughts.source_type, or both?
2. If both are supported, is thoughts.source_type intended to be a denormalized/generated/indexed projection of thoughts.metadata.source, or an independent field?
3. Should stock upsert_thought populate source_type from p_payload->>'source_type', from p_payload->'metadata'->>'source', or leave enhanced columns to follow-up updates?
4. Should dashboards and source filters query a compatibility expression such as coalesce(thoughts.source_type, thoughts.metadata->>'source')?
5. Should recipes write only the canonical representation, or should they write both fields for compatibility?
6. Would maintainers accept pull requests that document the canonical model, normalize recipes to that model, update dashboard queries, add a compatibility view/RPC/helper, and provide migration/backfill guidance?

Possible Contribution Path

1. Document the canonical source attribution model.
2. Clarify whether upsert_thought should populate enhanced columns from payload keys or metadata.
3. Add a read-side compatibility helper such as effective_source.
4. Update dashboard queries to use effective_source or an equivalent coalesce(...) expression.
5. Update recipes to write the canonical field consistently.
6. Add migration/backfill guidance for installations that already have only one representation.

Reference Links

Repository References

• Source filtering recipe:
  [recipes] Source filtering for Open Brain thoughts #30

• Slack message deduplication recipe:
  [recipes] Slack message deduplication pattern for thought ingestion #89

• Dashboard contribution merged as dashboards/open-brain-dashboard-next/:
  [dashboards] Full-featured web dashboard for thought management #111

• Enhanced thoughts columns adding thoughts.source_type:
  [schemas] Enhanced thoughts columns and utility RPCs #191

• Brain health monitoring views:
  [recipes] Brain health monitoring views #194

• Consolidation workers:
  [integrations] Consolidation workers (bio + metadata) #200

• Synthesis capture:
  [recipes] Synthesis capture — Query-as-Ingest pattern #212

• Brain stats daily and heatmap filter:
  [schemas] Brain stats daily + heatmap filter #221

• Wiki pipeline source fallback:
  [recipes] Wiki pipeline: OpenRouter + thought_edges + prompt safety #234

• Readwise capture/import:
  [integrations] Add Readwise highlight capture (backfill + webhook + schema) #237

• open-brain-rest gateway:
  [integrations] open-brain-rest — Cloudflare Worker REST gateway for the Next.js dashboard #239

• Standardize thought ingestion patterns:
  [architecture] Standardize thought ingestion patterns across recipes and integrations #61

• Prior type drift report and fix:
  [schemas] workflow-status migration fails: type column does not exist on thoughts table #182
  [repo] Sweep fix-now backlog issues #185

Discord Discussion References

• Discussion of controlled vocabulary inside thoughts.metadata:
  https://discord.com/channels/1481783256641699840/1481789897470906429/1482622942121689169

• Discussion of migrating/custom schema overlap involving source_type and JSONB metadata:
  https://discord.com/channels/1481783256641699840/1481790073392599041/1482874122747777095

• Discord notification/discussion for the Slack deduplication metadata recipe:
  https://discord.com/channels/1481783256641699840/1481790377534034101/1484302480127950879

[github-actions]

Hey @thewoolleyman — welcome to Open Brain Source! 👋

Thanks for opening your first issue. Someone from the community will take a look soon.

If you're interested in contributing (code or not), check out CONTRIBUTING.md — we have paths for developers and non-developers alike.