Schema
    The Solo Mom ProjectUpdated Jun 3, 2026MD

    The Solo Mom Project database schema

    Imported Markdown source from DOCS/database/schema-overview.md.

    Source file

    DOCS/database/schema-overview.md

    Markdown73 lines
    1. ---
    2. title: Schema Overview
    3. description: Primary relational model for auth, products, taxonomy, claims, contacts, and blog
    4. status: evolving
    5. lastUpdated: "2026-03-23 12:18 ET (America/New_York)"
    6. owner: Engineering
    7. ---
    9. # Schema Overview
    11. ## Definition File
    12. - `db/neon/schema.ts`
    14. ## Core Table Groups
    16. ### Auth (Better Auth)
    17. - `user`
    18. - `session`
    19. - `account`
    20. - `verification`
    22. ### App Profile and Products
    23. - `users` (app profile, includes `is_admin`)
    24. - `products`
    25. - `bookmarks`
    26. - `product_views`
    27. - `card_backgrounds`
    29. ### Taxonomy
    30. - `taxonomy_terms`
    31. - `taxonomy_synonyms`
    32. - `product_taxonomy`
    34. ### Claims and Moderation
    35. - `product_claims`
    36. - `product_edit_proposals`
    38. ### Submissions
    39. - `product_submissions`
    41. ### Contact Enrichment
    42. - `resource_contacts`
    44. ### Taxonomy Suggestion Runs
    45. - `taxonomy_suggestion_runs`
    46. - `taxonomy_suggestions`
    47. - `taxonomy_suggestion_actions`
    49. ### Blog (migration `0116`)
    50. - `blog_posts` — post content, status lifecycle, SEO fields, cover image
    51. - `blog_tags` — tag name/slug/description
    52. - `blog_post_tags` — many-to-many bridge with composite PK `(post_id, tag_id)`
    54. ### Editorial Corpus (migration `0118`)
    55. - `editorial_sources` — one row per persisted corpus source set, such as a WhatsApp export bundle
    56. - `editorial_source_threads` — per-chat thread records with counts and non-identifying source metadata
    57. - `editorial_source_items` — redacted message/system-event records ready for downstream annotation and retrieval work
    58. - `editorial_media_artifacts` — metadata-only attachment records and future derived-artifact lane
    59. - `editorial_ingestion_runs` — operator-run history for analyze/manifest/redact/persist workflows
    61. ### Editorial Retrieval Layer (migration `0119`)
    62. - `editorial_chunks` — contiguous retrieval windows derived from persisted source items
    63. - `editorial_observations` — typed retrieval annotations linked to chunk/item evidence
    64. - `editorial_entities` — normalized entities derived from recommendation observations and linked evidence keys
    66. See `DOCS/features/blog.md` for full schema details.
    68. ## Notes
    69. - Legacy categorization columns still exist in `products` (`categories`, `labels`, `tags`).
    70. - Hierarchical taxonomy links are represented via `product_taxonomy`.
    71. - Enrichment audit flags are stored in `products.enrichment_meta`.
    72. - Editorial corpus tables intentionally store redacted working-corpus records and ingestion metadata, not raw WhatsApp source text or raw chat/member labels.
    73. - Retrieval tables are additive overlays on the redacted corpus and are designed for internal editorial intelligence and downstream brief/draft grounding.