Skip to content

Data Model

This document is the canonical reference for the someli-api MySQL database schema. It was last reconciled against the schema dump in someli-schema.sql.

⚠ This document was substantially rewritten after a schema audit revealed the previous version was incomplete and contained inaccuracies. See §1 Schema Audit Findings below for the specific corrections.

1. Schema Audit Findings

A direct comparison between the previous version of this document and someli-schema.sql surfaced the following:

Finding Severity Detail
Total table count High Previous doc claimed "~140+ tables." Actual: 214 tables.
Tables documented with column detail High Previous doc covered ~30 tables. 184 tables had no documentation at all.
No foreign-key constraints in the schema High The schema defines zero FOREIGN KEY constraints. All 111 indexes are plain KEY/PRIMARY KEY declarations. Relationships shown in this doc are application-level — the database does not enforce them. Orphan rows are possible.
tAccounts.corpusName does not exist High The previous doc and rag-pipeline.md both reference a corpusName column on tAccounts. This column is not in the schema. Either the RAG corpus identifier is stored elsewhere (likely tAccounts.gics_code or another JSON column) or it was renamed. Cross-doc fix needed.
tJobs.jobId does not exist Medium The previous doc invented this column. The actual tJobs schema has id, type, detail, result, modelOutput, status, created, started, completed, isDeleted.
Job type-code list disagrees with jobs-inventory.md High The previous data-model.md listed DGEN, UGEN, UCL, TCC, PIC, CGEN, UMGEN, RAGCON, TSB, HPB, CLKB, TRCON, TCON. jobs-inventory.md lists CGEN, UGEN, DGEN, DPGEN, UMGEN, TIV, MCC, TCH, UCL, TSB, FC, TCON, RTCON, RGEN. These cannot both be right. The truth lives in the running tJobs.type values; recommend a SELECT DISTINCT type FROM tJobs to settle it.
tMember.email is misnamed in the previous doc Medium The login column is username. PersonalEmail is a separate column. There is no email column.
tMember.lastLogin is misnamed Low Actual column is last_login (snake_case).
tAccounts.accOwner is misnamed Low Actual column is owner. The doc's companyUrl is also wrong — the column is companyWebsite.
tMember.auth_pass defaults to a literal password Critical The schema defines auth_pass varchar(50) NOT NULL DEFAULT 'Welcome!1'. This is a hardcoded default credential at the database level. If any code path inserts a member without explicitly setting auth_pass, that user is created with the password "Welcome!1".
Schema name typos Low tAccontInsights (typo of tAccountInsights) coexists alongside the correctly-spelled tAccountInsights. Both have data. tTempalte_Status (typo of tTemplate_Status). tAccounts.stylePriorty (typo of stylePriority). These typos are now load-bearing — renaming will require app-code changes.
Sentinel datetime values Medium Multiple tables (tUserLibrary.lastScheduled, tMedia.lastScheduled, etc.) default to 1975-05-07 16:20:00. Any logic that filters on lastScheduled IS NULL or lastScheduled = '0000-00-00' will not behave as expected — the value is always set, with this magic date meaning "never scheduled."

Action items

  1. Reconcile the job type-code lists across data-model.md, jobs-inventory.md, and content-pipeline.md.
  2. Find where the RAG corpus identifier is actually stored — update rag-pipeline.md once known.
  3. Investigate whether the auth_pass default is ever exercised. If yes, treat as a security incident.
  4. Decide whether to fix the schema typos (tAccontInsights, tTempalte_Status, stylePriorty) or accept them and document them. Renames are migration work.
  5. Add real foreign-key constraints during the modular-monolith refactor if data integrity matters operationally — currently it relies entirely on application discipline.
  6. Investigate the 44 unreferenced tables (see §1.1 below) — many are likely abandoned features or superseded by other tables. Cleanup candidates.

1.1 Code-Reference Audit

A second-pass audit was run after the original count gap (140+ vs. 214) was noticed. Every table name was grepped against the entire codebase (all .js and .py files outside node_modules, .yarn, doc, .git). Results:

Reference count Tables Verdict
3 or more references 143 Definitely active
1–2 references 27 Real but light usage
Zero references 44 Genuinely unreferenced in this codebase

170 of 214 tables are referenced in this codebase's code; 44 are not.

Reconciling the 140 → 214 gap:

  • ~30 tables (out of the 74 difference) were missed by the original audit but are actually well-used — the original audit's grep methodology was incomplete.
  • ~44 tables exist in the schema with no code references — these are addressed in §7 Unreferenced Tables below.

Important caveat: "no code reference in this repo" ≠ "safe to drop." The web client, mobile clients, partner integrations, BI tools (Metabase / Looker / Tableau), out-of-band SQL scripts, or other repositories on the same database may write or read these tables. Verify ownership before any cleanup.

2. Database Access Patterns

Three MySQL client libraries are used concurrently — see Architecture Overview and Enterprise Readiness §5.4 for the consolidation recommendation.

Package Type Usage
mysql (via modules/dbDriver/lib/mysql.js) Async callbacks Generic CRUD layer (actions/actions.js), accessed via App.db
sync-mysql Synchronous/blocking Route handlers (con variable in routes/routes.js, routes/auth.js), most background jobs
mysql2/promise Async/await Newer job files, dashboard services, the better path

Generic CRUD layer (actions/actions.js)

Accepts any table name dynamically:

  • getAll(tableName, conditions, fields, limit, offset) — SELECT
  • insertData(tableName, fields) — INSERT
  • updateData(tableName, fields, conditions) — UPDATE
  • removeData(tableName, conditions) — DELETE
  • customQuery(rawSQL) — raw SQL execution

Naming convention

Most tables use the t prefix (tMember, tAccounts, tContentPlanner). A handful break this convention: baseModelAI, stripeEventsLog, trainingDataAI, userFileUploads, thelpCenter (lowercase t), tcompanyProfile (lowercase c), tuserFontsLibrary (lowercase u). These are inconsistencies that grew from individual contributions over time.

3. Hot Tables — Scale Indicators

The following auto-increment values give a rough sense of which tables receive the most traffic. Auto-increment is not row count (deletes don't decrement it), but it's a reliable signal of throughput.

Table Auto-increment Notes
tJobs 46,639,514 The job queue. Massive throughput; explains the polling-pattern findings in the readiness doc.
tAnalyticsLogs 5,222,417 Worth investigating retention policy
tSetting 4,658,350 One per (member, account) pair, typically
tSPostInsights 2,210,627 Per-post engagement snapshots over time
tEventLogs 1,904,409 Worth investigating retention policy
tAssetsLib 1,794,883 Asset library entries
tMediaLib 1,536,148 Stock + AI image library
tMedia 1,467,891 Generated design media
tDriveFilesChangeLog 1,071,335 Worth investigating retention policy
tLibrary 1,049,929 Master content library
tUploads 1,025,633 Every uploaded file
tTempalte_Status 641,122 (note: name typo)
tFeed_Status 607,617 RSS feed processing trail
tAutoDesignPost 255,291
tCPChannels 249,442 One per (post × channel)
tSAccountIns 233,666 Account-level insight snapshots
tImageCorrectionLog 230,246
tMultipleVariantTemp 212,245
tAutoDisconnectAccount 172,797
tUserFeedsLog 163,645
tContentPlanner 105,491 Scheduled posts

The fact that tJobs is at 46M without partitioning, retention, or archival is itself a finding — that table is heading toward operational pain if not already there.

4. Entity Relationship Diagram (Core Data Flow)

tMember (users)
  ├── tAccountMembers ──── tAccounts (multi-tenant accounts)
  │                            ├── tSubscriptions ──── tPricing ──── tProducts
  │                            ├── tSetting (one per member-account pair)
  │                            ├── tCompany_Website_Info (scraped company data)
  │                            ├── tBrandPositioning (AI-generated)
  │                            ├── tObjective (AI-generated)
  │                            ├── tRecommendedSub (AI-generated topics)
  │                            ├── tUserFolder ── tUserLibrary ── tUserMedia
  │                            ├── tAccountTempSet ── tFavTemplateset ── tDefaultTemplates
  │                            └── tContentPlanner (scheduled posts)
  │                                    ├── tCPChannels ── tCPChannelAccounts
  │                                    ├── tGeneratedFiles
  │                                    └── tSPosts (published) ── tSPostInsights
  ├── tMemberAuth (social-platform OAuth tokens)
  │       └── tDefaultAccount ── tSAccounts ── tSAccountIns
  ├── tOrganization (company profile)
  └── tStripe (Stripe customer reference)

tLibrary (master content library)
  └── tMedia ── tUploads (generated design images)

tJobs (background-job queue, ~46M throughput)
tEmailSchedule (email-sending queue)

Reminder: none of these arrows are enforced foreign keys. Treat as application-level conventions, not schema constraints.

5. Table Definitions by Domain (Detailed)

The following sections give detailed column listings for the most important and most-changed tables. For the full 214-table catalog, see §6 Full Table Catalog.

5.1 Users & Authentication

tMember

The primary user table. ~4,800 rows (auto-increment 4783).

Column Type Description
id bigint unsigned PK Primary key
username varchar(100) Login credential — usually email-shaped but stored here, not in email
firstName, lastName varchar(50) User name
sid varchar(100) Session-style identifier
password longtext Bcrypt-hashed password (per bcryptjs usage)
auth_pass varchar(50) ⚠ Defaults to literal 'Welcome!1'. Investigate use; treat as security finding.
auth_token, access_token text / longtext API auth tokens
reset_token text Password-reset flow
created, updated timestamp Audit timestamps
last_login timestamp Last successful login
role varchar(250) Default 'User'
role_type int Default 5. The != 11 check in user-specific-ai.md filters by this column
logo_id, company_logo int FK-style references to tUploads.id
organization_id int FK-style to tOrganization.id
company, companyWebsite, company_info text/varchar Company profile fields, also duplicated in tAccounts
address, city, country, iso2, mobile various Contact info
PersonalEmail varchar(100) Separate from username
partnerId bigint Affiliate partner reference (was partner_id in previous doc — wrong)
referredBy, sponsorId varchar/int Affiliate / referral chain
mfa_code varchar(45) MFA one-time-code temp storage
defaultPaymentId tinyint 1=Stripe, 2=Paddle (per the tAccounts comment)
landing_url, landing_accountId, S_url various Landing-page tracking
whatsApp, includeWhatsapp varchar/tinyint WhatsApp contact opt-in
enableStripe, enableBeta tinyint Feature flags
isCancelled, Cancelledby tinyint/int Soft-cancel
isDeleted, Deletedby tinyint/int Soft-delete
isOnline tinyint Presence flag
isMailVerified, isMailVerifiedStatus tinyint/varchar Email verification
profiled tinyint Onboarding-completion flag
lastSubId, lastCatId bigint Recent-subject / recent-category cache
noId int Numbered identifier (purpose unclear from schema alone)

Indexes: PRIMARY (id), idx_tMember_isDeleted (id, isDeleted).

tMemberAuth

Social-platform OAuth credentials per member. ~7,000 rows. Tokens themselves live in JSON columns, not as discrete fields — see note below.

Column Type Description
id bigint unsigned PK
accountId, member_Id, provider_Id bigint / int App-level FKs (account, member, social platform: 1=FB, 3=IG, 4=LI, 6=TikTok by convention)
providerAuth varchar(100) Provider-specific auth identifier
verified tinyint Verified-by-provider flag
detail json Holds the access / refresh tokens, scopes, and provider response. All actual credentials live here.
acctResp json Provider account response payload at link-time
FbAccounts, IgAccounts, LnAccounts, TwtAccounts, TiktokAccounts json Per-platform sub-account / page lists when a single OAuth grant covers multiple accounts
token_status varchar(50) Health status of the token
tactive tinyint Active flag
Added_at, Updated_at timestamp Audit timestamps
expiryDate timestamp Token expiry
removed, revoked_at tinyint / timestamp Removed-by-user trail
refresh_revoked, refresh_at tinyint / timestamp Refresh-token state
isinvalid, last_error, last_check_at, last_response, last_error_at various Error / health-check audit trail
new_error, new_response, new_error_at various Most recent error context (rolling buffer pattern)
isDisabled, isDeleted tinyint Soft-disable / soft-delete flags

Indexes: PRIMARY (id), idx_member_auth (id, provider_Id, isDeleted, isDisabled, removed).

⚠ Tokens-in-JSON anti-pattern. access_token, refresh_token, and token_secret are stored as keys inside detail rather than as discrete columns. Consequences:

  • Not indexable — you can't WHERE detail.access_token = ?; queries that need to look up by token shape are full scans.
  • Harder to audit — token rotation requires JSON manipulation rather than a simple UPDATE.
  • Harder to rotate — there's no schema-level invariant that the structure is consistent across rows; provider-specific shapes evolve over time.

The previous version of this doc claimed these were discrete columns. They are not. Anyone querying based on the prior doc will write broken queries. Migration to discrete columns is a worthwhile schema cleanup project.

tOrganization

Company / organization profile.

Column Description
id PK
name Organization name
company_logo Reference to tUploads.id
footer Footer image reference
industry_id FK-style
address, city, state, country, zip Location
companyUrl Website URL

5.2 Accounts & Multi-tenancy

tAccounts

The multi-tenant container. ~2,100 rows (auto-increment 2137). Significantly more columns than the previous doc described.

Column Type Description
id bigint unsigned PK
accountName varchar(100) (Previous doc called this company — wrong; actual is accountName)
companyWebsite varchar(100) (Previous doc called this companyUrl — wrong)
AccountStatus varchar(50)
created, updated timestamp
customer_id varchar(255) Stripe / Paddle customer ID
paymentGateway varchar(100) Default 'stripe'
defaultPaymentId bigint 1=Stripe, 2=Paddle (per inline COMMENT)
owner bigint App-level FK to tMember.id (previous doc called this accOwner — wrong)
profiled, isDeleted tinyint Status flags
isRagUpdated, isCorpusUpdated tinyint RAG-pipeline progress flags
isAiRecSub_created, isAiContentCreated tinyint AI-content generation gates
stylePriorty json ⚠ Column-name typo — should be stylePriority
ai_preferred_styles, ai_preferred_key_words, user_preferred_key_words json Brand-design preferences
style_priority_updated_date, keywords_updated_date, industry_updated_date timestamp When AI preferences were last refreshed
isKeywordsAndStylesFetched, isIndustryFetched tinyint Caching flags
Onboarding_type_id, noId int Onboarding type and a numbered identifier
TempsetId, favTempSetId bigint/int Default and favorite template-set IDs
gics_code, industry_classifications json GICS taxonomy + AI-derived classifications
suggestedTempSetIds json Recommended template sets
hasRSSFeed, RFeed_check_time, hasRSSFeed_check_time tinyint/timestamp RSS feed tracking
reelIdeasCreated tinyint
lastSubId, lastCatId bigint Recent-use caches
CouponId int Active coupon

Note: the previous doc claimed a corpusName column for the Vertex AI RAG corpus identifier. This column does not exist. The corpus reference must live elsewhere — likely a separate table (e.g., tCloudKnowledgeBase). Cross-doc fix required for rag-pipeline.md.

tAccountMembers

Many-to-many between members and accounts.

Column Description
id PK, memberId, accountId App-level FKs
role Role within this account
isDeleted

tDefaultAccount

Default social-account configuration per member-account pair.

Column Description
id, memberId, account_Id, mauthId, channelId, isActive, isDeleted Standard fields

5.3 Subscriptions & Billing

tSubscriptions

Column Description
id, accountId, pricing_id, paddleSubId, stripeSubId, status, start_date, end_date, isDeleted Standard fields

tPricing

Column Description
id, product_id, price, description, paddlePriceId, sandboxPriceId, stripePriceId, isDeleted Standard fields

tProducts

Column Description
id, name, type

tStripe

Stripe customer reference per member.

Column Description
id, user_id, customer_id, subscription_id

5.4 Content Planner & Scheduling

tContentPlanner

The central scheduling table. ~105K rows.

Column Type Description
id PK bigint unsigned
accountId, memberId bigint unsigned App-level FKs
created, replaced, assigned, scheduledFor, publishOn, updated timestamp Scheduling lifecycle
type bigint default 1 Post type code
source int Where the post originated
scheduledMediaId, replacedMediaId bigint unsigned Media references
thumbnailUrl, mediaUrl varchar(500) S3 URLs
content varchar(5000) Post text
firstComment varchar(5000) First-comment text
has_comment, isBranding, is_reel, isfavourite, fromCusLibrary, sameContentForAll, attachLogo, cancelled, approved, isDeleted tinyint Status flags
status, live_Id varchar Lifecycle status + platform live ID
height, width, aspectratio, duration int/double/varchar Media dimensions
subId, catId, libraryId bigint Subject / category / library references
bColor, aColor varchar(25) Brand / accent color overrides
retryCount bigint Retry attempts
tiktokConfiguration json TikTok-specific publishing config
Link varchar(650) External link if any

Indexes: 4 composite indexes (idx_contentplanner_opt_1 through idx_contentplanner_opt_4) tuned for the polling and listing query patterns.

tCPChannels

One row per (post × channel).

Column Description
id, contentPlannerId, channelId, isDeleted

tCPChannelAccounts

Specific social accounts within a channel for a post.

Column Description
id, CPChannelId, mauthId, postId, status, isDeleted

tGeneratedFiles

Column Description
id, postId, imageId, generatedImage, generatedJson, generatedthumbs, scheduledTime, scheduleStatus, isDeleted

tFeed_Posts

Column Description
id, member_Id, scheduled_at, isCancelled, isDeleted

5.5 Social Media Accounts & Analytics

tSAccounts

Connected social-media accounts tracked for analytics.

Column Description
id, sAccountId, isDeleted

tSPosts

Published posts (platform post IDs).

Column Description
id, sAccountId, postId, cpId, mediaId, isDeleted

tSAccountIns

Column Description
id, sAccountId, captureOn, followers, following, likes

tSPostInsights

~2.2M rows. Per-post engagement snapshots over time.

Column Description
id, sPostId, captured
likes, comments, shares, views, impressions, clicks Current snapshot metrics
glikes, gcomments, gshares, gviews, gimpressions, gclicks, gengagement Growth (delta) metrics
isDeleted

tMonthlyAccountInsights and tMonthlyPostsInsights

Monthly aggregates plus per-month PDF report references.

5.6 Content Library & Media

tLibrary

Master content library. ~1M rows.

Column Description
Id PK, content, comment, header, title, body, footer Structured copy
subjectId, catId App-level FKs
refCount, genMedia, lastScheduled Usage tracking
iSearchTerms AI-generated image-search terms
isDeleted

tUserLibrary

Account-specific content library. ~44K rows.

Column Description
Id PK, content, accountId, member_id, created, updated, isDeleted, refCount
header, title, body, footer, comment Structured copy
isApproved, subjectId, catId, catGroupId, user_edited_count
language_id, tone_of_voice_id Localization + voice settings
lastScheduled Defaults to sentinel 1975-05-07 16:20:00
imageIdeas, iSearchTerms json — AI-generated image prompts
addedAssets, custContent_Id, folderId, fromCategoryGroup, isArchive

Indexes on member_id, subjectId, catId, lastScheduled, accountId, isDeleted.

tMedia

Generated design images from master library. ~1.5M rows.

Column Description
Id PK, upload_id, library_id, created, updated
variants, libCode, mediaApproved, mediaRejected, rejectedReason, isRemoved, refCount
json varchar(500) — S3 URL of Polotno JSON
orgId, lastScheduled (sentinel-defaulting), jobId, refId, tempId
brandColor, accentColor varchar(25)
checkedQueue, verifiedBy, contentCheck, checkedValidQueue, misMatch, validatedAt Validation pipeline state
isAIValidated (1=done, 2=not started), colorValidated (0=processing, 1=done, 2=not started), imageValidated (same scale)
retryCount

Indexes: idx_media_opt_1 (checkedQueue, imageValidated, colorValidated, misMatch, created), idx_media_opt_2 (lastScheduled, checkedQueue, misMatch, mediaRejected, isRemoved, created), idx_media_opt_3 (refId). These are already present and were the targets of the §3.x performance fix recommendations.

tUserMedia, tUploads, tMediaLib, tCusUpload

Standard column layouts; see schema dump for specifics.

5.7 Templates & Design

tDefaultTemplates, tUserTemplates, tFavTemplateset, tAccountTempSet

See previous doc for column-level detail; columns largely match the schema.

5.8 Subjects, Categories & Topics

tSubjects, tDefaultCategories (with rag_active flag), tOrgSubjects

Standard layouts.

5.9 Settings & Configuration

tSetting

~4.6M rows. One per (member, account) pair.

tLanguageModels

AI model parameters per feature × language.

5.10 AI Content & Brand Intelligence

tCompany_Website_Info, tBrandPositioning, tObjective, tRecommendedSub

Per user-specific-ai.md.

5.11 Jobs & Processing Queue

tJobs

The job queue. ~46.6M auto-increment — the busiest table in the system.

Column Type Description
id bigint unsigned PK
type varchar(10) Job-type code. Verify the live distinct values — see audit finding above
detail json Per-job parameters
result json Output
modelOutput longtext (Not in previous doc)
status tinyint NOT NULL DEFAULT 0 0=pending, 1=processing, 2=success, 3=retry, 4=final-failure (per content-pipeline.md)
created, started, completed timestamp Lifecycle markers
isDeleted tinyint NOT NULL DEFAULT 0

Indexes: separate single-column indexes on type, status, isDeleted. No composite index combining these for the polling pattern — exactly the gap flagged in the recent query-performance investigation. Adding (type, status, isDeleted, started, id) would dramatically reduce CPU.

The previously-documented jobId column does not exist. Removed.

5.12 Folders & User Content Organization

tUserFolder, tUserSharedFolders

Standard.

5.13 Email & Notifications

tEmailSchedule

Email-sending queue (processed by job_send_mail.js). ~86K rows.

Column Type Description
Id int PK
Email varchar(100) Recipient address
Status varchar(50) String values: 'Inserted' (pending), 'Delivered' (sent), 'Pending' (failed). Note: 'Pending' is overloaded to mean "failed" rather than "not yet processed" — confusing but in active use
TemplateId varchar(500) SendGrid dynamic-template ID
Description varchar(500) Human-readable purpose
DetailedInfo json Template variables for the merge
accountId, Member_Id bigint App-level FKs
Added_time, Updated_time, Scheduled_time, Sent_time timestamp Lifecycle timestamps

⚠ Two corrections vs. the previous version of this doc:

  1. Status is string-based, not numeric. The previous doc claimed 0 / 1 / 2 integer values. The actual column is varchar(50) with the string values listed above. Anyone querying WHERE Status = 0 will match nothing.
  2. Two columns missed: Updated_time (auto-updated on row change) and Sent_time (when SendGrid accepted) were missing from the previous doc.

See also notifications.md for an open question about job_send_mail.js filtering by a single TemplateId.

5.14 Industry Classification

tIndustriesList, tSubIndustriesList, tIndustryGroups, tSectorsMain, tGlobalIndustryClassificationStd

GICS taxonomy + ad-hoc industry tables.

6. Full Table Catalog

All 214 tables grouped by inferred domain. Tables marked (detailed above) have full column definitions in §5.

The Used? column shows code-reference status as of the audit: - ✅ — referenced 3+ times in the codebase (active) - ⚠ — referenced 1–2 times (light usage; verify before depending) - ❌ — zero code references (see §7 for likely cause)

Tables in this list that are not in §5 are catalog-only — their column-level details should be added when they become subjects of work. Each line is a one-sentence description of purpose.

Identity & Authentication (9)

Used? Table Notes
✅ tMember (detailed above) Primary user table
✅ tMemberAuth (detailed above) Social-platform OAuth tokens
✅ tAuthProviders OAuth provider catalog
✅ tPersonnel Internal staff records
✅ tPersonnelAccounts Staff-to-account assignments
✅ tPartnerAuth Partner-API authentication
✅ tRoles Role catalog
✅ tUserRoles User-to-role assignments
❌ tSessions Session storage — likely from a previous session-storage mechanism (app uses in-memory chatSessions Map)

Accounts & Multi-tenancy (10)

Used? Table Notes
✅ tAccounts (detailed above) Tenant container
✅ tAccountMembers (detailed above) Member-account link
✅ tAccountManagers Account-manager assignments
✅ tAccountTempSet (detailed above) Template sets per account
⚠ tAccountActivelog Activity log per account (1 ref — write-only)
✅ tAccount_feeds RSS feeds attached to an account
✅ tAccount_websites Websites tracked per account
✅ tAccount_Website_News Scraped news per account-website
❌ tAccountsTokens Account-scoped tokens — not wired up; tMemberAuth is used instead
✅ tDefaultAccount (detailed above) Default social-account config

Subscriptions, Billing & Affiliate (15)

Used? Table Notes
✅ tSubscriptions (detailed above)
✅ tPricing (detailed above)
✅ tProducts (detailed above)
✅ tStripe (detailed above) Stripe customer reference
✅ tStripe_webhooks Stripe webhook event log
✅ stripeEventsLog (note: lowercase prefix) Stripe event audit
✅ tPaddle_webhooks Paddle webhook event log
✅ tpaddle_notifi_mail (note: lowercase prefix) Paddle notification email queue
❌ tPaymentGateway Gateway catalog — never read in code
✅ tBillingPeriods Billing-period definitions
✅ tCoupon Coupons
✅ tCurrencies Currency catalog
✅ tUserBillingInfo Per-user billing info
✅ tImpactConversion Impact.com affiliate conversions
⚠ tImpactItemDetails Impact.com item details (1 ref)
✅ tAffiliateMarketingDetails Affiliate program details
❌ tAffiliatePartner Affiliate partners — internal affiliate tables look abandoned; tImpactConversion is the active path
❌ tAffiliatePricing Affiliate-specific pricing — same
✅ tChannelPartner Channel partners

Content Planner & Scheduling (10)

Used? Table Notes
✅ tContentPlanner (detailed above)
✅ tCPChannels (detailed above)
✅ tCPChannelAccounts (detailed above)
❌ tCPUserActivity Activity log per content-planner post — never written
✅ tGeneratedFiles (detailed above) Merged design output
✅ tFeed_Posts (detailed above)
⚠ tFeed_log RSS-feed processing trail (1 ref — write-only INSERT IGNORE)
✅ tFeed_Status RSS-feed status table (~600K rows)
❌ tDynamic_Queue Looks like an abandoned queue mechanism predating tJobs
⚠ tHolidays Holiday calendar (1 ref)
✅ tFeedback User feedback / surveys

Social Media & Analytics (12)

Used? Table Notes
✅ tSAccounts (detailed above)
✅ tSAccountIns (detailed above)
✅ tSAccountTokens Per-social-account tokens
✅ tSPosts (detailed above)
✅ tSPostInsights (detailed above) ~2.2M rows
❌ tAccountInsights Account-level insights — likely superseded by tMonthlyAccountInsights
❌ tAccontInsights ⚠ Schema typo of the above. Both tables have data, neither is read by the code
✅ tMonthlyAccountInsights (detailed above)
✅ tMonthlyPostsInsights (detailed above)
✅ tPostInsights (Distinct from tSPostInsights — verify the difference)
❌ tPostSource Post source classification — never queried
❌ tSharedPostSource Shared-post source records — never queried
✅ tAnalyticsLogs ~5.2M rows. General analytics audit

Content Library, Media & Uploads (~32)

Used? Table Notes
✅ tLibrary (detailed above) ~1M rows
❌ tLibrary_copy Backup / snapshot of tLibrary — likely a one-time migration artifact
✅ tLibraryUploadLog Library import-log
✅ tUserLibrary (detailed above)
✅ tMasterLibrary Master content master-set
✅ tCustomLibrary Custom user library entries
✅ tCustContent Custom content storage
✅ tTextLibrary Text-only library
✅ tMedia (detailed above) ~1.5M rows
✅ tUserMedia Per-user generated design media
✅ tMasterMedia Master media catalog
✅ tCusMedia Custom user media
⚠ tMediaContent Media-content metadata (2 refs)
✅ tMediaLib (detailed above) Stock + AI image library (~1.5M)
❌ tMediaSource Media source catalog — sources stored as varchar values in tMediaLib.mediaSource instead
✅ tUploads (detailed above) ~1M rows
✅ tCusUpload Custom user uploads
✅ tFolderUploads Uploads scoped to a folder
✅ userFileUploads (note: no t prefix) User file uploads
✅ tDesignUpload Design-specific uploads
⚠ tImageJobs Image generation job records (2 refs — INSERT-only sites visible)
✅ tImageCorrectionLog Image-correction audit (~230K rows)
⚠ tImageTemplate Image-template catalog (1 ref)
❌ tCarousals Carousel posts — note: actual carousels go through tContentPlanner; this looks abandoned
✅ tCarousalTemplates Carousel template definitions
✅ tReelVideos Reel video records
✅ tUserReelIdeas User reel-ideas
❌ tCustomCategory Custom user categories — likely superseded by tCategoryGroups
⚠ tAssetsLib ~1.8M rows. Generic asset library (only 2 refs — large data, light reads)
✅ tFonts Font catalog
✅ tuserFontsLibrary (note: lowercase u) Per-user font library

Templates & Design (14)

Used? Table Notes
✅ tDefaultTemplates (detailed above)
✅ tUserTemplates (detailed above)
✅ tFavTemplateset (detailed above)
⚠ tTemplateLogs Template-action audit (1 ref)
❌ tTemplatesGroup Template grouping — tCategoryGroups is the active grouping table
✅ tTempalte_Status ⚠ Name typo — should be tTemplate_Status. ~640K rows.
✅ tMultipleVariantTemp Multi-variant templates (~210K)
✅ tNOTemplates "NO" prefix = onboarding templates? Verify
✅ tCategoryTemp Category-template mapping
✅ tScheduleTempsets Schedule-template sets
✅ tStyles Style catalog
⚠ tDiy_text DIY text snippets (1 ref — random sample query)
✅ tColors Color catalog
✅ tLogo Logo records

Subjects, Categories, Topics (15)

Used? Table Notes
✅ tSubjects (detailed above)
✅ tNOSubjects NO-prefix subjects (onboarding?)
✅ tOrgSubjects (detailed above)
✅ tDefaultCategories (detailed above) With rag_active flag
✅ tCategories Category catalog
✅ tCategoryGroups Category groupings
⚠ tTopics Topic catalog (2 refs)
✅ tTopicLists Topic-list groupings
✅ tTopicEvents Topic events
❌ tEvents Event catalog — never read or written
❌ tSubjectContent Subject-content mapping — never used
❌ tSubjectFiles Subject-attached files — never used
⚠ tSubRCat Subject-recommended-category mapping (2 refs)
⚠ tIndSubMap Industry-subject map (1 ref)
❌ tMapCategories Category mappings — never used

Settings & Configuration (16)

Used? Table Notes
✅ tSetting (detailed above) ~4.6M rows
✅ tAdminPreferenceSetting Admin-side preferences
❌ tPreferences User preferences — likely superseded by tSetting and tAdminPreferenceSetting
❌ tDesign_default_setting Design defaults — never used
❌ tBusinessGroup Parent business-group table — feature uses tBusinessGroupInformation and tBusinessGroupSetting instead
✅ tBusinessGroupInformation Business-group metadata
⚠ tBusinessGroupSetting Business-group preferences (2 refs)
⚠ tDefaultFolder Default folder structure (1 ref)
✅ tToneOfVoice Tone-of-voice catalog
✅ tLanguages Language catalog
✅ tLanguageModels (detailed above) AI model params per feature
❌ baseModelAI (note: no t prefix) Base AI model definitions — likely superseded by tLanguageModels
❌ tIndustryAI Industry-AI mapping — likely superseded by tAccounts.industry_classifications JSON
❌ trainingDataAI (note: lowercase prefix) AI training-data — likely superseded by tTrainingData
✅ tTrainingData Training data
⚠ tSamplePrompt Sample prompts (2 refs)

AI Content, Brand & RAG (17)

Used? Table Notes
✅ tCompany_Website_Info (detailed above) Scraped company data
✅ tCompanyDetails Detailed company metadata
⚠ tCompanyOverviews Company overviews (2 refs)
❌ tcompanyProfile (note: lowercase c) — likely superseded by tCompanyDetails / tCompanyOverviews
✅ tBrandPositioning (detailed above) AI-generated brand positioning
✅ tBrandPositioningGroup Brand-positioning grouping
✅ tBrandPositioningQuestions Brand Q&A template
✅ tObjective (detailed above) AI-generated objectives
⚠ tGoals_Objectives Goals & objectives library (1 ref)
✅ tRecommendedSub AI-recommended subjects
✅ tCorporateLogs Corporate-event audit (used by user-specific AI jobs)
✅ tConversation Conversation transcripts (likely from agents)
❌ tCopies Copy variants — feature not completed
✅ tAiContentHistory AI-content generation history
✅ tAiPostFixLogs AI post-fix audit
✅ tCloudKnowledgeBase RAG corpus references — likely where corpusName actually lives. Verify
✅ tRephrased_Translated_Content Translated/rephrased copy
❌ tPreproductionPost Pre-production posts — workflow not completed
✅ tAutoDesignPost Auto-design post records (~250K)
✅ tReference Reference materials

Jobs & Processing Queue (2)

Used? Table Notes
✅ tJobs (detailed above) ~46.6M auto-increment
❌ tCronStatus Cron-job status tracking — never wired up

Folders & User Organization (5)

Used? Table Notes
✅ tUserFolder (detailed above)
✅ tUserSharedFolders (detailed above)
❌ tDriveFolder Google Drive folder integration — likely an aborted feature
❌ tDriveFiles Google Drive file integration — likely an aborted feature
✅ tDriveFilesChangeLog ~1M rows. Drive-file change audit (active despite the above being unused — verify what writes here)

Email & Notifications (4)

Used? Table Notes
✅ tEmailSchedule (detailed above) Email queue (~86K)
✅ tEmailLogs Email send audit
✅ tEmailUserNotification User-notification preferences for email
✅ tMessages In-app messages

Industry Classification (6)

Used? Table Notes
✅ tGlobalIndustryClassificationStd GICS taxonomy reference
⚠ tIndustriesList Industry catalog (2 refs)
⚠ tSubIndustriesList Sub-industry catalog (2 refs)
⚠ tIndustryGroups Industry groupings (2 refs)
⚠ tSectorsMain Top-level sectors (2 refs)
✅ tNetOrg Net-org classification

Geographic Reference (5)

Used? Table Notes
⚠ tCities City catalog (1 ref — single getCities endpoint)
✅ tCountries Country catalog
⚠ tStates State / province catalog (2 refs)
❌ tTimeZone Timezone catalog — tMaptimezones is the active timezone-mapping table
⚠ tMaptimezones Timezone mapping (2 refs)

Channels (1)

Used? Table Notes
❌ tChannels Social-channel catalog — channels are referenced as raw channelId ints, not joined

Variants (2)

Used? Table Notes
❌ tVariants Generic variant definitions — never used
✅ tSVariants Subject-variant mapping

Token Management (1)

Used? Table Notes
✅ tAutoDisconnectAccount ~170K rows. Auto-disconnect tracking for expired tokens

Logging & Audit (8)

Used? Table Notes
✅ tEventLogs ~1.9M rows. General event audit
❌ tActivity Activity records — likely superseded by tEventLogs or tCorporateLogs
❌ tActivityLog Activity audit — same
❌ tUserActivity User-activity audit (~133K rows but no code refs — written by code that no longer runs?)
✅ tUserContentStatus User-content status records
✅ tUserFeaturesLog Feature-usage audit
✅ tUserFeedsLog Feed-action audit (~163K)
✅ tUFeedChangeLog Feed-change audit

Onboarding (4)

Used? Table Notes
✅ tOnboarding_types Onboarding-type definitions
✅ tOnboardingtype_pricing Onboarding-tier pricing
✅ tUserInvitation User-invitation records
✅ tUserInvitationCorp Corporate user invitations

Status & Lifecycle (3)

Used? Table Notes
✅ tCatContent_Status Category-content status
❌ tContent_Status Content-status catalog — statuses stored as varchar values directly, not FK-joined
❌ tContentTypes Content-type definitions — same

Misc / System (14)

Used? Table Notes
✅ tApiKeys Per-feature API-key storage (e.g., Pexels / Pixabay)
❌ tAuthor Author records — never used
❌ tBeta_features Beta-feature flags — feature flags don't actually work via this table
❌ tExcelMaster Excel-import master records — actively-used Excel jobs reference different tables
✅ tGhl_Appointments GoHighLevel appointment integration
✅ thelpCenter (note: lowercase t) Help-center articles
✅ tInventory Inventory records
✅ tOrganization (detailed above) Organization profile
✅ tRequests Generic request records
❌ tSomeliData App-specific data records — never used
⚠ tSources Source catalog (2 refs)
✅ tTermsAgreementLog Terms-acceptance audit
⚠ tTermsAgreementVersion Terms version history (2 refs)
✅ tTestMember Test-member fixture

7. Unreferenced Tables Detail

The following 44 tables exist in the schema with zero references in this codebase as of the audit. Grouped by likely cause. Each row should be investigated before any cleanup decision.

Likely-deprecated / superseded by other tables

Table Probable replacement
tAccountInsights tMonthlyAccountInsights
tAccontInsights Schema typo of the above; both tables hold data, neither is read
tActivity, tActivityLog, tUserActivity Likely tEventLogs or tCorporateLogs
baseModelAI, trainingDataAI tLanguageModels, tTrainingData
tLibrary_copy One-time backup or migration artifact
tSessions App now uses in-memory chatSessions Map
tPreferences tSetting, tAdminPreferenceSetting
tCronStatus Worker status not centrally tracked
tcompanyProfile (lowercase c) tCompanyDetails, tCompanyOverviews
tIndustryAI tAccounts.industry_classifications JSON
tCustomCategory tCategoryGroups
tTemplatesGroup tCategoryGroups
tVariants tSVariants (subject-scoped variants)
tTimeZone tMaptimezones

Likely abandoned features

Table Notes
tDriveFiles, tDriveFolder Google Drive integration that wasn't completed
tCarousals Carousel feature works via tContentPlanner + tCarousalTemplates; this table is unused
tBeta_features Beta-flag table — feature flags don't work via this
tBusinessGroup Parent table; feature uses tBusinessGroupInformation and tBusinessGroupSetting
tPreproductionPost Pre-production workflow not completed
tCopies Copy-variant feature not completed
tDynamic_Queue Looks like an abandoned queue mechanism predating tJobs

Catalog tables that should be referenced but aren't

These are reference / lookup tables. Code typically uses varchar values directly rather than FK-joining.

Table Notes
tChannels Channels stored as channelId int directly
tContent_Status, tContentTypes Statuses / types stored as varchar values
tPostSource, tMediaSource, tSharedPostSource Source values stored directly
tEvents, tMapCategories Standalone catalogs not wired in

Genuinely unreferenced (purpose unclear from name alone)

Table Notes
tAccountsTokens Account-scoped tokens — tMemberAuth is the active path
tAffiliatePartner, tAffiliatePricing Internal affiliate tables — tImpactConversion is the active integration
tAuthor Generic author records
tCPUserActivity Content-planner activity log — never written
tDesign_default_setting Design-defaults table
tExcelMaster Excel-import master — active jobs use different tables
tPaymentGateway Payment-gateway catalog
tSomeliData Generic data table
tSubjectContent, tSubjectFiles Subject-attachment tables

Investigation guide

For each table above, investigate via:

SELECT
  table_name,
  table_rows,                      -- approximate row count
  data_length + index_length AS size_bytes,
  update_time,                     -- last write
  create_time
FROM information_schema.tables
WHERE table_schema = '<your-db>'
  AND table_name IN ('tAccountInsights', 'tDriveFiles', /* ... etc */)
ORDER BY update_time DESC;

Categorize each result:

  • Empty + never updated → safe to drop after stakeholder confirmation
  • Has data + recent updates → something is writing to it. Find the writer (other repos, manual SQL, BI tools, mobile clients) before assuming dead
  • Has data + no recent updates → likely frozen historical data. Decide between archival or drop

Cleanup is a Phase-2 modular-monolith activity, not Phase 0. But starting the investigation now (information_schema query, ask stakeholders) is cheap.

8. Key Relationships Summary

Parent Child App-level FK column Cardinality
tMember tAccountMembers memberId 1:N
tMember tMemberAuth member_Id 1:N
tMember tSetting member_id 1:N (per account)
tAccounts tAccountMembers accountId 1:N
tAccounts tSubscriptions accountId 1:1 active
tAccounts tContentPlanner accountId 1:N
tAccounts tUserLibrary accountId 1:N
tAccounts tUserFolder accountId 1:N
tMemberAuth tDefaultAccount mauthId 1:1
tContentPlanner tCPChannels contentPlannerId 1:N
tCPChannels tCPChannelAccounts CPChannelId 1:N
tContentPlanner tGeneratedFiles postId 1:N
tSAccounts tSPosts sAccountId 1:N
tSPosts tSPostInsights sPostId 1:N
tLibrary tMedia library_id 1:N
tUserLibrary tUserMedia library_id 1:N
tMedia / tUserMedia tUploads upload_id N:1
tDefaultTemplates tFavTemplateset fav_set_id N:1
tFavTemplateset tAccountTempSet tempsetId 1:N
tSubjects tIndustriesList indId N:1
tSubjects tSectorsMain secId N:1
tLanguageModels tTrainingData lmId 1:N
tSubscriptions tPricing pricing_id N:1
tPricing tProducts product_id N:1
tBrandPositioning tBrandPositioningQuestions question_id N:1

Reminder: all foreign-key relationships above are application-level only. The schema enforces nothing. Adding real FOREIGN KEY constraints during the modular-monolith refactor is recommended.

9. Cross-Doc Consistency Issues to Resolve

Issue Affected docs Action
tAccounts.corpusName referenced as the RAG corpus identifier, but no such column exists rag-pipeline.md § Data Sources, this doc (now corrected) Find the actual location (likely tCloudKnowledgeBase table); update rag-pipeline.md
Job type-code lists disagree data-model.md (now removed inline list), jobs-inventory.md, content-pipeline.md Run SELECT type, COUNT(*) FROM tJobs GROUP BY type against the live DB; settle the canonical list
tMember column names This doc (now corrected); any code that referenced email / lastLogin / partner_id Verify code paths use the right names
Schema typos (tAccontInsights, tTempalte_Status, stylePriorty) This doc Decide: fix in schema (migration work) or accept and document
tMember.auth_pass DEFAULT 'Welcome!1' security.md (security finding) Investigate every code path that inserts into tMember to confirm auth_pass is always explicitly set; rotate any users with this default

10. Total Tables Identified

214 tables total. Previous claim of "~140+" was a significant under-count.

Of the 214: 170 are referenced in the codebase, 44 are not. The "~140+" figure from the original audit reflected an incomplete scan — it missed ~30 actively-used tables and didn't surface the ~44 truly-unused ones. See §1.1 Code-Reference Audit and §7 Unreferenced Tables Detail.

Domain Count
Identity & Authentication 9
Accounts & Multi-tenancy 10
Subscriptions, Billing & Affiliate ~19
Content Planner & Scheduling ~11
Social Media & Analytics 13
Content Library, Media & Uploads ~32
Templates & Design 14
Subjects, Categories & Topics 15
Settings & Configuration 16
AI Content, Brand & RAG 20
Jobs & Processing Queue 2
Folders & User Organization 5
Email & Notifications 4
Industry Classification 6
Geographic Reference 5
Channels 1
Variants 2
Token Management 1
Logging & Audit 8
Onboarding 4
Status & Lifecycle 3
Misc / System 14
Total ~214

Counts are approximate due to overlap (some tables fit multiple domains).

The core data flow remains: Members own Accounts which have Settings, Subscriptions, Social-platform Auth connections, and Content Library items. Library items flow into the Content Planner for scheduling, get Generated Files (merged images with branding), publish through CP Channels to social platforms, and analytics are tracked in SPosts / SPostInsights / Monthly Insights tables. Around this core, ~20 supporting catalogs and ~30 logging / status / audit tables accumulated over time — many of which are good candidates for consolidation or archival as part of the modular-monolith refactor.