Accounts Hub Cheat Sheet

Canonical reference for how Accounts connects to Profiles, Business Directory, Channels, and Affiliate systems.

Updated: 2026-06-05T13:36:01-05:00

Client Dashboard

Quick Diagnostics

Tenant, canonical file locations, DB existence, SQLite versions, and row counts.

Tenant
filmassist
PHP: 8.1.34
PDO Drivers: mysql, odbc, pgsql, sqlite
DB Files
accounts.sqlite
/home1/sys9/public_html/database/tenants/systmzro/accounts.sqlite
Exists: Yes
business-directory.sqlite
/home1/sys9/public_html/database/tenants/systmzro/business-directory.sqlite
Exists: Yes
Counts
accounts.accounts 0
accounts.accounts_affiliate_codes 0
profiles.profiles —
profiles.profile_views —
business_directory.bd_listings 0
network.channels 117
network.videos 99
network.channel_categories 17
network.video_categories 9
network.channel_follows 5
network.comments 12
network.ratings 13
network.battles 0
network.battle_votes 0
network.channel_sections 0
network.channel_section_items 0
network.complaints 0
network_analytics.network_events 2941
network_analytics.video_watch_sessions 0
network_analytics.video_watch_pings 0
NDJSON Files
/home1/sys9/public_html/core/assets/data/tenants/filmassist/business-directory/stats.ndjson Missing
/home1/sys9/public_html/core/assets/data/tenants/filmassist/products/affiliates/referrals.ndjson Missing

Cheat Sheet

Filter sections to quickly find fields, file paths, and contracts.

Accounts (Hub)

File locations
  • /core/assets/database/tenants/<tenant>/accounts.sqlite
accounts
Canonical identity + cross-plugin bridge pointers.
Key fields
  • id (PK)
  • username, email, password
  • role, account_status, email_verified, verification_status
  • profile_id, business_listing_id, channel_id, affiliate_id
  • parent_affiliate_id
Contracts
  • Bootstrap rule: when an account is created, pointers default to id (profile_id=business_listing_id=affiliate_id=channel_id=id).
  • Never rely on other plugins for identity display; always load username/email from accounts first.
accounts_affiliate_codes
Referral/share key registry (key -> account_id).
Key fields
  • affiliate_key (PK, NOCASE)
  • account_id
  • kind (code|slug)
  • created_at, updated_at
Contracts
  • Resolve ?ref=... using accounts_affiliate_codes first.
  • Keys are unique case-insensitively.
Preferred helpers
  • accounts_current_tenant()
  • accounts_db($tenant): ?PDO
  • accounts_get_by_id($pdo, $id): ?array
  • accounts_affiliate_ensure_keys($pdo, $accountId, $username): array{code,slug}
  • accounts_resolve_parent_affiliate_id($pdo, $refKey, $default=100002): int
Routes
  • /client-main-dashboard (example hub consumer route)
  • /accounts-cheatsheet (this page)

Profiles

File locations
  • Current implementation (SQLite): /core/assets/data/tenants/<tenant>/profiles/profiles.sqlite
  • Analytics (SQLite): /core/assets/data/tenants/<tenant>/profiles/analytics.sqlite
profiles
Public-facing profile record (safe fields, visibility).
Key fields
  • id (PK)
  • user_id (UNIQUE) // often equals accounts.id
  • first, last, city, state_province, country, phone_number
  • company, occupation, picture1, bio
  • headline, website, instagram, youtube, tiktok, twitter
  • visibility_level (0/1/2), is_public (legacy)
  • created_at, updated_at
Contracts
  • Bridge: accounts.profile_id should map to profiles.id (recommended id=user_id=accounts.id).
  • Use visibility_level as the canonical access control (2=Public, 1=Members, 0=Private).
profile_views
Append-only analytics for profile views (member vs anonymous).
Key fields
  • id (PK AUTOINCREMENT)
  • profile_id
  • viewer_user_id (NULL=anonymous)
  • viewed_at_ts, viewed_at_iso
Contracts
  • Use profiles_track_view($profileId, $viewerUserIdOrNull).
  • Breakdown: viewer_user_id NULL => anonymous.
Preferred helpers
  • profiles_get_by_id($profileId): ?array
  • profiles_get_by_user_id($userId): ?array
  • profiles_upsert_for_user($userId, $fields): array{profile,created,saved}
  • profiles_track_view($profileId, ?$viewerUserId): bool
  • profiles_views_total($profileId): int
  • profiles_views_breakdown_total($profileId): array{anonymous,members}
Note
Profiles is SQLite-backed here (not JSON). The /assets/data location is a legacy convention for this plugin.

Business Directory

File locations
  • /core/assets/database/tenants/<tenant>/business-directory.sqlite
  • Stats NDJSON (fallback): /core/assets/data/tenants/<tenant>/business-directory/stats.ndjson
bd_listings
Business listing record (dashboard references this by pointer id).
Key fields
  • id (PK) // maps to accounts.business_listing_id
  • slug (used for public URLs when available)
  • plan_slug (used to show plan name / subscription tier)
Contracts
  • Bridge: accounts.business_listing_id -> bd_listings.id.
  • Public URL patterns commonly use either ?id=<id> or ?slug=<slug>.
Preferred helpers
  • bd_db($tenant): ?PDO (if business-directory-plugin.php provides it)
  • bd_listing_views_last_days($listingId, $days, $tenant): int (if available)
  • bd_products_active_subscription($accountId, $vendorTenant): ?array (if bridge installed)

Network (Film Assist TV)

File locations
  • /core/assets/database/tenants/<tenant>/network.sqlite
  • /core/assets/database/tenants/<tenant>/network-analytics.sqlite
channels
Creator/channel record. Commonly 1 channel per user.
Key fields
  • id (PK) // often set = user_id for stability
  • user_id (UNIQUE NOT NULL) // maps to accounts.id
  • slug, name, tagline, description
  • logo_url, cover_url
  • is_public, is_verified, is_featured
  • allow_comments_default, allow_ratings_default
  • moderation_status (approved|pending|rejected|banned)
  • approved_by_user_id/approved_at, rejected_by_user_id/rejected_at/rejection_reason, banned_by_user_id/banned_at/ban_reason
  • created_at, updated_at
Contracts
  • Bridge: accounts.channel_id -> channels.id (recommended: channels.id = accounts.id).
  • Public display should filter is_public=1 and moderation_status=approved (if the column exists).
videos
Video records tied to a channel; may reference external providers (YouTube/Rumble/Twitch/etc).
Key fields
  • id (PK)
  • user_id (NOT NULL) // owner (often accounts.id)
  • channel_id (NOT NULL) // FK to channels.id
  • provider, external_id, source_url
  • title, description, thumbnail_url
  • duration_seconds, published_at
  • visibility (public|unlisted|private), is_featured
  • allow_comments, allow_ratings
  • moderation_status + audit fields
  • created_at, updated_at
Contracts
  • Public display should filter visibility=public and moderation_status=approved (if the column exists).
  • If you add video slug URLs, use a UNIQUE constraint on (channel_id, slug).
taxonomy
Categories for channels/videos; seeded from JSON and synced into SQLite.
interactions
Follows, comments, ratings, battles, votes, complaints.
analytics
Watch sessions and event tracking (separate SQLite).
Preferred helpers
  • network_current_tenant()
  • network_db_file($tenant): string
  • network_analytics_db_file($tenant): string
  • network_pdo($tenant): ?PDO
  • network_analytics_pdo($tenant): ?PDO
  • network_get_tv_stats($tenant): array{channels,videos,members}
  • network_upsert_channel_for_user($userId, $fields, $tenant): array{channel,created,saved}
  • network_channel_set_moderation_status($channelId, $status, $actorUserId, $reason, $tenant): bool
  • network_video_set_moderation_status($videoId, $status, $actorUserId, $reason, $tenant): bool
  • network_complaint_create($data, $tenant): int
  • network_complaint_set_status($complaintId, $status, $reviewedByUserId, $action, $notes, $tenant): bool
  • network_watch_start(...), network_watch_ping(...), network_watch_end(...)
Routes
  • /tv-home
  • /tv-search
  • /tv-trending
  • /tv-showcase
  • /tv-showcase-details
  • /tv-creators
  • /tv-creator-details
  • /tv-plans
  • /tv-plan-details
  • /channel-home
  • /channel-videos
  • /video-details
  • /report-video
  • /report-channel
  • /admin-network-dashboard
  • /admin-network-channels
  • /admin-network-videos
  • /admin-network-complaints

Affiliate (Keys + Lineage + Referrals)

File locations
  • Accounts keys: /core/assets/database/tenants/<tenant>/accounts.sqlite (accounts_affiliate_codes)
  • Referrals NDJSON (products): /core/assets/data/tenants/<vendorTenant>/products/affiliates/referrals.ndjson
Contracts
  • Lineage: accounts.parent_affiliate_id is the “upline”. Downline is accounts where parent_affiliate_id = this account id.
  • Key registry: accounts_affiliate_codes maps share keys to account ids.
  • Referral counts (if using NDJSON): file lines use affiliate_user_id and ts; count by affiliate_user_id and time window.