Data Dictionary
Auto-generated from the live database schema. 30 tables.
Tables
activities(2)activity_log(62)campaigns(0)cases(3)client_verticals(6)clients(11)component_objects(17)components(1)contacts(8)entities(7)ghl_calendars(2)ghl_campaigns(0)ghl_contacts(610)ghl_custom_fields(47)ghl_locations(5)ghl_opportunities(1362)ghl_pipelines(8)ghl_tags(62)ghl_users(0)kb_articles(6)leads(10)meta_account_insights(64)meta_ad_accounts(1)meta_ad_insights(456)meta_ads(41)meta_adsets(9)meta_campaigns(7)opportunity_ratings(0)opportunity_research(3)sync_log(515)
activities
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| client_id | INTEGER | FK | — |
| contact_id | INTEGER | FK | — |
| type | TEXT | NOT NULL | — |
| summary | TEXT | NOT NULL | — |
| details | TEXT | — | |
| created_by | TEXT | 'system' | |
| created_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
activity_log
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| entity_type | TEXT | NOT NULL | — |
| entity_id | INTEGER | NOT NULL | — |
| action | TEXT | NOT NULL | — |
| detail | TEXT | — | |
| created_at | TEXT | datetime('now') |
campaigns
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| client_id | INTEGER | NOT NULLFK | — |
| name | TEXT | NOT NULL | — |
| channel | TEXT | — | |
| status | TEXT | 'active' | |
| budget | REAL | 0 | |
| start_date | TEXT | — | |
| end_date | TEXT | — | |
| created_at | TEXT | datetime('now') |
cases
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| lead_id | INTEGER | FK | — |
| client_id | INTEGER | NOT NULLFK | — |
| title | TEXT | NOT NULL | — |
| status | TEXT | 'intake' | |
| priority | TEXT | 'normal' | |
| assigned_to | TEXT | — | |
| procedure_type | TEXT | — | |
| estimated_value | REAL | 0 | |
| notes | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') | |
| closed_at | TEXT | — |
client_verticals
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| client_id | INTEGER | NOT NULLFK | — |
| entity_id | INTEGER | NOT NULLFK | — |
| config | TEXT | '{}' | |
| created_at | TEXT | datetime('now') |
clients
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| name | TEXT | NOT NULL | — |
| contact_name | TEXT | — | |
| contact_email | TEXT | — | |
| contact_phone | TEXT | — | |
| specialty | TEXT | — | |
| billing_model | TEXT | 'flat_retainer' | |
| billing_rate | REAL | 0 | |
| status | TEXT | 'active' | |
| notes | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') | |
| entity_id | INTEGER | FK | — |
| ghl_location_id | TEXT | — | |
| gridlink_id | TEXT | — | |
| deleted | INTEGER | 0 |
component_objects
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| component_type | TEXT | NOT NULL | — |
| object_type | TEXT | NOT NULL | — |
| enabled | INTEGER | 1 | |
| last_sync_at | TEXT | — | |
| record_count | INTEGER | 0 | |
| sync_cursor | TEXT | — | |
| error | TEXT | — |
components
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| type | TEXT | NOT NULL | — |
| name | TEXT | NOT NULL | — |
| enabled | INTEGER | 1 | |
| config | TEXT | '{}' | |
| sync_frequency_min | INTEGER | 15 | |
| status | TEXT | 'not_connected' | |
| last_sync_at | TEXT | — | |
| next_sync_at | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') |
contacts
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| gridlink_id | TEXT | NOT NULL | — |
| client_id | INTEGER | NOT NULLFK | — |
| first_name | TEXT | NOT NULL | — |
| last_name | TEXT | NOT NULL | — |
| title | TEXT | — | |
| TEXT | — | ||
| phone | TEXT | — | |
| mobile_phone | TEXT | — | |
| is_main | INTEGER | 0 | |
| notes | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') | |
| ghl_user_id | TEXT | — | |
| deleted | INTEGER | 0 |
entities
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| name | TEXT | NOT NULL | — |
| type | TEXT | NOT NULL | — |
| status | TEXT | 'active' | |
| description | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') | |
| dba_name | TEXT | — |
ghl_calendars
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| calendar_type | TEXT | — | |
| event_type | TEXT | — | |
| is_active | INTEGER | 1 | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_campaigns
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| status | TEXT | — | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_contacts
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| first_name | TEXT | — | |
| last_name | TEXT | — | |
| contact_name | TEXT | — | |
| company_name | TEXT | — | |
| TEXT | — | ||
| phone | TEXT | — | |
| source | TEXT | — | |
| tags | TEXT | '[]' | |
| city | TEXT | — | |
| state | TEXT | — | |
| postal_code | TEXT | — | |
| address | TEXT | — | |
| date_of_birth | TEXT | — | |
| date_added | TEXT | — | |
| date_updated | TEXT | — | |
| custom_fields | TEXT | '[]' | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_custom_fields
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| field_key | TEXT | — | |
| data_type | TEXT | — | |
| model | TEXT | — | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_locations
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| TEXT | — | ||
| phone | TEXT | — | |
| address | TEXT | — | |
| city | TEXT | — | |
| state | TEXT | — | |
| timezone | TEXT | — | |
| client_id | INTEGER | FK | — |
| synced_at | TEXT | datetime('now') |
ghl_opportunities
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| status | TEXT | — | |
| monetary_value | REAL | 0 | |
| pipeline_ghl_id | TEXT | — | |
| pipeline_stage_ghl_id | TEXT | — | |
| contact_ghl_id | TEXT | — | |
| assigned_to | TEXT | — | |
| source | TEXT | — | |
| created_at | TEXT | — | |
| updated_at | TEXT | — | |
| last_status_change_at | TEXT | — | |
| last_stage_change_at | TEXT | — | |
| custom_fields | TEXT | '[]' | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_pipelines
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | NOT NULL | — |
| stages | TEXT | '[]' | |
| date_added | TEXT | — | |
| date_updated | TEXT | — | |
| synced_at | TEXT | datetime('now') | |
| deleted | INTEGER | 0 |
ghl_users
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| ghl_id | TEXT | NOT NULL | — |
| location_ghl_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| TEXT | — | ||
| phone | TEXT | — | |
| role | TEXT | — | |
| contact_id | INTEGER | FK | — |
| synced_at | TEXT | datetime('now') |
kb_articles
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| slug | TEXT | NOT NULL | — |
| title | TEXT | NOT NULL | — |
| parent_slug | TEXT | — | |
| sort_order | INTEGER | 0 | |
| content | TEXT | NOT NULL | '' |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') |
leads
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| client_id | INTEGER | NOT NULLFK | — |
| campaign_id | INTEGER | FK | — |
| first_name | TEXT | NOT NULL | — |
| last_name | TEXT | NOT NULL | — |
| TEXT | — | ||
| phone | TEXT | — | |
| source | TEXT | — | |
| status | TEXT | 'new' | |
| score | INTEGER | 0 | |
| notes | TEXT | — | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') |
meta_account_insights
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| meta_account_id | INTEGER | NOT NULLFK | — |
| date_start | TEXT | NOT NULL | — |
| date_stop | TEXT | NOT NULL | — |
| impressions | INTEGER | 0 | |
| clicks | INTEGER | 0 | |
| ctr | REAL | 0 | |
| cpc | REAL | 0 | |
| cpm | REAL | 0 | |
| spend | REAL | 0 | |
| reach | INTEGER | 0 | |
| frequency | REAL | 0 | |
| conversions | INTEGER | 0 | |
| cost_per_conversion | REAL | 0 | |
| actions | TEXT | '[]' | |
| cost_per_action_type | TEXT | '[]' | |
| synced_at | TEXT | datetime('now') |
meta_ad_accounts
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| client_id | INTEGER | NOT NULLFK | — |
| ad_account_id | TEXT | NOT NULL | — |
| ad_account_name | TEXT | '' | |
| access_token | TEXT | '' | |
| status | TEXT | 'active' | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') |
meta_ad_insights
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| meta_account_id | INTEGER | NOT NULLFK | — |
| ad_id | TEXT | — | |
| adset_id | TEXT | — | |
| campaign_id | TEXT | — | |
| date_start | TEXT | NOT NULL | — |
| date_stop | TEXT | NOT NULL | — |
| impressions | INTEGER | 0 | |
| clicks | INTEGER | 0 | |
| ctr | REAL | 0 | |
| cpc | REAL | 0 | |
| cpm | REAL | 0 | |
| spend | REAL | 0 | |
| reach | INTEGER | 0 | |
| frequency | REAL | 0 | |
| conversions | INTEGER | 0 | |
| cost_per_conversion | REAL | 0 | |
| actions | TEXT | '[]' | |
| cost_per_action_type | TEXT | '[]' | |
| video_views | INTEGER | 0 | |
| video_p25_watched | INTEGER | 0 | |
| video_p50_watched | INTEGER | 0 | |
| video_p75_watched | INTEGER | 0 | |
| video_p100_watched | INTEGER | 0 | |
| synced_at | TEXT | datetime('now') |
meta_ads
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| meta_account_id | INTEGER | NOT NULLFK | — |
| ad_id | TEXT | NOT NULL | — |
| adset_id | TEXT | — | |
| campaign_id | TEXT | — | |
| name | TEXT | — | |
| status | TEXT | — | |
| effective_status | TEXT | — | |
| creative_id | TEXT | — | |
| creative_body | TEXT | — | |
| creative_title | TEXT | — | |
| creative_link_url | TEXT | — | |
| creative_image_url | TEXT | — | |
| creative_thumbnail_url | TEXT | — | |
| created_time | TEXT | — | |
| updated_time | TEXT | — | |
| synced_at | TEXT | datetime('now') |
meta_adsets
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| meta_account_id | INTEGER | NOT NULLFK | — |
| adset_id | TEXT | NOT NULL | — |
| campaign_id | TEXT | — | |
| name | TEXT | — | |
| status | TEXT | — | |
| effective_status | TEXT | — | |
| daily_budget | REAL | — | |
| lifetime_budget | REAL | — | |
| budget_remaining | REAL | — | |
| bid_amount | REAL | — | |
| bid_strategy | TEXT | — | |
| billing_event | TEXT | — | |
| optimization_goal | TEXT | — | |
| targeting | TEXT | '{}' | |
| start_time | TEXT | — | |
| end_time | TEXT | — | |
| created_time | TEXT | — | |
| updated_time | TEXT | — | |
| synced_at | TEXT | datetime('now') |
meta_campaigns
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| meta_account_id | INTEGER | NOT NULLFK | — |
| campaign_id | TEXT | NOT NULL | — |
| name | TEXT | — | |
| objective | TEXT | — | |
| status | TEXT | — | |
| effective_status | TEXT | — | |
| buying_type | TEXT | — | |
| daily_budget | REAL | — | |
| lifetime_budget | REAL | — | |
| budget_remaining | REAL | — | |
| bid_strategy | TEXT | — | |
| special_ad_categories | TEXT | '[]' | |
| created_time | TEXT | — | |
| updated_time | TEXT | — | |
| start_time | TEXT | — | |
| stop_time | TEXT | — | |
| synced_at | TEXT | datetime('now') |
opportunity_ratings
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| opportunity_ghl_id | TEXT | NOT NULL | — |
| purchase_ability | INTEGER | 0 | |
| interest_level | INTEGER | 0 | |
| notes | TEXT | — | |
| rated_by | TEXT | 'unknown' | |
| created_at | TEXT | datetime('now') | |
| updated_at | TEXT | datetime('now') |
opportunity_research
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| opportunity_ghl_id | TEXT | NOT NULL | — |
| contact_name | TEXT | — | |
| search_results | TEXT | '{}' | |
| summary | TEXT | — | |
| searched_at | TEXT | datetime('now') |
sync_log
| Column | Type | Constraints | Default |
|---|---|---|---|
| id | INTEGER | PK | — |
| component_type | TEXT | NOT NULL | — |
| object_type | TEXT | NOT NULL | — |
| started_at | TEXT | datetime('now') | |
| finished_at | TEXT | — | |
| status | TEXT | 'running' | |
| records_fetched | INTEGER | 0 | |
| records_created | INTEGER | 0 | |
| records_updated | INTEGER | 0 | |
| error_detail | TEXT | — |