ComplyAI Data Dictionary
Complete reference for all data entities, fields, and definitions
Table of Contents
- Customer Domain
- Ad Account Domain
- Ad Content Domain
- Compliance Domain
- Operational Domain
- Junction Tables
1. Customer Domain
1.1 Users
Table Name: users
Service: complyai-core
Description: Individual users who access the ComplyAI platform
| Field | Type | Nullable | Description | Example | PII |
|---|---|---|---|---|---|
id | INTEGER | No | Primary key, auto-increment | 42 | No |
sub_id | VARCHAR(100) | Yes | Auth0/OAuth subject ID | `auth0 | 123abc` |
email | VARCHAR(255) | No | User's email address (unique) | jane@company.com | ✅ Yes |
name | VARCHAR(200) | Yes | User's display name | Jane Smith | ✅ Yes |
avatar | VARCHAR(200) | Yes | URL to profile picture | https://cdn.../avatar.jpg | No |
email_verified | BOOLEAN | No | Email verification status | true | No |
paid | BOOLEAN | No | Legacy payment status | false | No |
linked_accounts | BOOLEAN | No | Has linked Facebook accounts | true | No |
reviewed | BOOLEAN | Yes | Account has been reviewed by admin | true | No |
subscribed | BOOLEAN | No | Has active subscription | true | No |
stripe_customer_id | VARCHAR(50) | Yes | Stripe customer identifier | cus_ABC123 | No |
user_access_token | VARCHAR(300) | Yes | Facebook user access token (legacy) | EAAG... | 🔐 Sensitive |
_encrypted_user_access_token | BINARY | Yes | Encrypted Facebook token | [binary] | 🔐 Sensitive |
terms_accepted | BOOLEAN | No | User accepted terms of service | true | No |
fs_uniquifier | VARCHAR(64) | No | Flask-Security unique ID | abc123... | No |
active | BOOLEAN | No | Account active status | true | No |
confirmed_at | TIMESTAMP | Yes | Email confirmation time | 2024-01-15 10:30:00 | No |
Relationships:
- Has many
ActivityEvents - Has many
FacebookPages - Belongs to many
Organizations(viauser_organizations) - Has many
Roles(via Flask-Security)
1.2 Organizations
Table Name: organizations
Service: complyai-core
Description: Client companies using ComplyAI platform
| Field | Type | Nullable | Description | Example | PII |
|---|---|---|---|---|---|
id | INTEGER | No | Primary key | 15 | No |
name | VARCHAR(100) | No | Organization name | Acme Advertising | No |
contact_person | VARCHAR(100) | Yes | Primary contact name | John Director | ✅ Yes |
subscription_plan | VARCHAR(25) | Yes | Current plan tier | professional | No |
subscription_start_date | TIMESTAMP | Yes | Plan start date | 2024-01-01 00:00:00 | No |
subscription_end_date | TIMESTAMP | Yes | Plan end date | 2024-12-31 23:59:59 | No |
calendly_meeting_start_datetime | TIMESTAMP | Yes | Onboarding call start | 2024-01-05 14:00:00 | No |
calendly_meeting_end_datetime | TIMESTAMP | Yes | Onboarding call end | 2024-01-05 15:00:00 | No |
vip | BOOLEAN | No | VIP customer flag | false | No |
stripe_plan | VARCHAR(50) | Yes | Stripe price ID | price_ABC123 | No |
trial_days | INTEGER | Yes | Trial period length | 14 | No |
stripe_subscription_id | VARCHAR(50) | Yes | Stripe subscription ID | sub_ABC123 | No |
active | BOOLEAN | Yes | Organization is active | true | No |
average_score | FLOAT | Yes | Average ad compliance score | 85.5 | No |
average_text_score | FLOAT | Yes | Average text analysis score | 88.2 | No |
average_media_score | FLOAT | Yes | Average media analysis score | 82.8 | No |
created_time | TIMESTAMP | No | Record creation time | 2024-01-01 10:00:00 | No |
updated_time | TIMESTAMP | No | Last update time | 2024-06-15 14:30:00 | No |
Relationships:
- Has many
Users(viauser_organizations) - Has many
OrgBusinessAccounts - Has many
Notifications
Computed Properties:
user_names: List of all user names in organizationuser_emails: List of all user emails in organization
1.3 Roles
Table Name: roles
Service: complyai-core
Description: User permission roles (Flask-Security)
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 1 |
name | VARCHAR(80) | No | Role name | admin |
description | VARCHAR(255) | Yes | Role description | System administrator |
permissions | TEXT | Yes | JSON permission set | ["read","write"] |
Standard Roles:
| ID | Name | Description |
|---|---|---|
| 1 | admin | Full system access |
| 2 | client | Standard customer user |
| 3 | developer | Development team access |
| 4 | viewer | Read-only access |
2. Ad Account Domain
2.1 OrgBusinessAccounts
Table Name: org_business_accounts
Service: complyai-core
Description: Meta Business Manager accounts linked to organizations
| Field | Type | Nullable | Description | Example | PII |
|---|---|---|---|---|---|
id | INTEGER | No | Primary key | 100 | No |
facebook_id | VARCHAR(100) | No | Meta Business Manager ID | 123456789012345 | No |
name | VARCHAR(200) | No | Business Manager name | Acme BM | No |
role | VARCHAR(25) | Yes | User's role in BM | ADMIN | No |
user_name | VARCHAR(50) | Yes | Associated user name | Jane Smith | ✅ Yes |
user_id | INTEGER | Yes | FK to users table | 42 | No |
organization_id | INTEGER | No | FK to organizations | 15 | No |
client_bm_su_access_token | VARCHAR(300) | Yes | System user token (legacy) | EAAG... | 🔐 Sensitive |
_encrypted_client_bm_su_access_token | BINARY | Yes | Encrypted system user token | [binary] | 🔐 Sensitive |
system_user_id | VARCHAR(50) | Yes | Meta system user ID | 12345678901234567 | No |
average_score | FLOAT | Yes | Average compliance score | 86.7 | No |
average_text_score | FLOAT | Yes | Average text score | 89.1 | No |
average_media_score | FLOAT | Yes | Average media score | 84.3 | No |
created_time | TIMESTAMP | No | Record creation | 2024-02-01 09:00:00 | No |
updated_time | TIMESTAMP | No | Last update | 2024-06-20 11:30:00 | No |
Relationships:
- Belongs to
Organization - Has many
OrgAdAccounts
2.2 OrgAdAccounts
Table Name: org_ad_accounts
Service: complyai-core
Description: Facebook Ad Accounts linked to business managers
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 500 |
name | VARCHAR(200) | No | Ad account name | Acme - US Market |
facebook_id | VARCHAR(100) | No | Ad account ID with prefix | act_123456789 |
org_business_account_id | INTEGER | Yes | FK to business account | 100 |
average_score | FLOAT | Yes | Average compliance score | 87.2 |
average_text_score | FLOAT | Yes | Average text score | 90.0 |
average_media_score | FLOAT | Yes | Average media score | 84.4 |
currency | VARCHAR(50) | Yes | Account currency | USD |
account_status | INTEGER | Yes | Meta account status code | 1 |
account_status_updated_time | TIMESTAMP | Yes | Status last checked | 2024-06-25 08:00:00 |
funding_source_type | INTEGER | Yes | Payment method type | 1 |
funding_source_display_string | VARCHAR(100) | Yes | Payment display text | Visa ****1234 |
cai_status | VARCHAR(50) | Yes | ComplyAI tracking status | active |
cai_status_updated_time | TIMESTAMP | Yes | CAI status update time | 2024-06-25 08:00:00 |
created_time | TIMESTAMP | No | Record creation | 2024-02-15 10:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-25 08:00:00 |
Account Status Codes:
| Code | Meaning |
|---|---|
| 1 | Active |
| 2 | Disabled |
| 3 | Unsettled |
| 7 | Pending Review |
| 8 | Pending Closure |
| 9 | In Grace Period |
| 100 | Pending Risk Review |
| 101 | Pending Settlement |
| 201 | Any Active |
| 202 | Any Closed |
Relationships:
- Belongs to
OrgBusinessAccounts - Has many
OrgAds
2.3 AdAccounts (API Service)
Table Name: ad_accounts
Service: complyai-api
Description: Detailed ad account data synced from Meta
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 1000 |
account_id | VARCHAR(50) | No | Meta account ID (no prefix) | 123456789 |
account_name | VARCHAR(200) | Yes | Account display name | Acme Campaigns |
owner | VARCHAR(200) | Yes | Account owner info | Acme Inc |
account_status | INTEGER | Yes | Status code (see above) | 1 |
disable_reason | VARCHAR(200) | Yes | Reason if disabled | Policy violation |
amount_spent | BIGINT | Yes | Total spend (in cents) | 1500000 |
balance | BIGINT | Yes | Current balance | 50000 |
currency | VARCHAR(25) | Yes | Currency code | USD |
business_city | VARCHAR(25) | Yes | Business city | San Francisco |
business_country_code | VARCHAR(50) | Yes | Country code | US |
business_name | VARCHAR(200) | Yes | Registered business name | Acme Inc |
business_street | VARCHAR(200) | Yes | Street address | 123 Market St |
business_street2 | VARCHAR(200) | Yes | Address line 2 | Suite 100 |
business_zip | VARCHAR(40) | Yes | Postal code | 94102 |
fb_created_time | TIMESTAMP | Yes | When created on Meta | 2023-01-15 00:00:00 |
timezone_name | VARCHAR(200) | Yes | Account timezone | America/Los_Angeles |
account_age | INTEGER | Yes | Account age in days | 540 |
funding_source | VARCHAR(200) | Yes | Payment source ID | 12345 |
funding_source_details | JSON | Yes | Payment details | {"type":"credit_card"} |
is_prepay_account | BOOLEAN | Yes | Prepaid account flag | false |
spend_cap | BIGINT | Yes | Spending limit | 10000000 |
capabilities | TEXT[] | Yes | Account capabilities | ["CUSTOM_AUDIENCES"] |
min_campaign_group_spend_cap | INTEGER | Yes | Min campaign budget | 100 |
min_daily_budget | INTEGER | Yes | Min daily budget | 100 |
offsite_pixels_tos_accepted | BOOLEAN | Yes | Pixel TOS accepted | true |
has_migrated_permission | BOOLEAN | Yes | Permission migration status | true |
is_personal | INTEGER | Yes | Personal account flag | 0 |
bm | VARCHAR(50) | Yes | Business Manager ID | 987654321 |
cai_status | VARCHAR(50) | Yes | ComplyAI status | monitoring |
cai_status_updated_time | TIMESTAMP | Yes | Status update time | 2024-06-25 08:00:00 |
token | VARCHAR(25) | Yes | Token identifier | primary |
created_time | TIMESTAMP | No | Record creation | 2024-01-20 12:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-25 08:00:00 |
2.4 AdAccountWebhookSubscriptions
Table Name: ad_account_webhook_subscriptions
Service: complyai-api
Description: Tracks webhook subscriptions per ad account
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 200 |
account_id | VARCHAR(50) | No | Ad account ID | 123456789 |
webhook_subscription | BOOLEAN | No | Has webhook sub | true |
ad_rule_new_ad | BOOLEAN | No | New ad rule active | true |
ad_rule_changes_on_ad_effective_status | BOOLEAN | No | Status change rule | true |
created_time | TIMESTAMP | No | Record creation | 2024-03-01 09:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-03-01 09:00:00 |
3. Ad Content Domain
3.1 OrgAds
Table Name: org_ads
Service: complyai-core
Description: Individual ads within ad accounts
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 5000 |
name | VARCHAR(200) | No | Ad name | Summer Sale Banner |
facebook_id | VARCHAR(100) | No | Meta ad ID | 23849203842934 |
status | VARCHAR(50) | Yes | Ad status | ACTIVE |
effective_status | VARCHAR(50) | Yes | Effective delivery status | ACTIVE |
account_id | VARCHAR(50) | Yes | Parent account ID | 123456789 |
ad_account_id | VARCHAR(50) | Yes | Account ID with prefix | act_123456789 |
account_name | VARCHAR(100) | Yes | Account name | Acme Campaigns |
account_currency | VARCHAR(50) | Yes | Currency | USD |
campaign_id | VARCHAR(50) | Yes | Parent campaign ID | 23849000000 |
campaign_effective_status | VARCHAR(50) | Yes | Campaign status | ACTIVE |
authorization_category | VARCHAR(50) | Yes | Ad category | NONE |
effective_authorization_category | VARCHAR(50) | Yes | Effective category | NONE |
page_id | VARCHAR(50) | Yes | Facebook page ID | 987654321 |
page_name | VARCHAR(100) | Yes | Page name | Acme Store |
title | TEXT | Yes | Ad title/headline | 50% Off Summer Sale |
text | TEXT | Yes | Primary text copy | Shop our biggest... |
description | TEXT | Yes | Ad description | Limited time offer |
image_url | TEXT | Yes | Image URL | https://cdn.../img.jpg |
image_hash | VARCHAR(500) | Yes | Image hash | abc123def456 |
video_url | TEXT | Yes | Video URL | https://cdn.../vid.mp4 |
video_id | VARCHAR(100) | Yes | Video asset ID | 23849000001 |
call_to_action_type | VARCHAR(100) | Yes | CTA button type | SHOP_NOW |
destination_url | TEXT | Yes | Landing page URL | https://acme.com/sale |
facebook_reason_for_disapproved | TEXT | Yes | Rejection reason | Policy violation: ... |
spend | FLOAT | Yes | Total spend | 1500.50 |
cpm | FLOAT | Yes | Cost per 1000 impressions | 12.50 |
cpc | FLOAT | Yes | Cost per click | 0.85 |
org_ad_account_id | INTEGER | Yes | FK to OrgAdAccounts | 500 |
created_time | TIMESTAMP | No | Record creation | 2024-04-01 10:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-25 08:00:00 |
Status Values:
| Status | Description |
|---|---|
ACTIVE | Ad is running |
PAUSED | Ad paused by user |
DELETED | Ad deleted |
ARCHIVED | Ad archived |
PENDING_REVIEW | Awaiting Meta review |
DISAPPROVED | Rejected by Meta |
PREAPPROVED | Pre-approved (limited) |
PENDING_BILLING_INFO | Awaiting payment |
CAMPAIGN_PAUSED | Parent campaign paused |
ADSET_PAUSED | Parent ad set paused |
IN_PROCESS | Being processed |
WITH_ISSUES | Has delivery issues |
3.2 OrgAdsScore
Table Name: org_ads_score
Service: complyai-core
Description: AI-generated compliance scores for ads
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 10000 |
score | FLOAT | Yes | Overall compliance score (0-100) | 85.5 |
text_score | FLOAT | Yes | Text analysis score (0-100) | 88.2 |
media_score | FLOAT | Yes | Media analysis score (0-100) | 82.8 |
db_ad_id | INTEGER | Yes | FK to OrgAds | 5000 |
Score Interpretation:
| Range | Risk Level | Action |
|---|---|---|
| 90-100 | Low | Likely to pass |
| 70-89 | Medium | Review recommended |
| 50-69 | High | Changes suggested |
| 0-49 | Critical | Major issues |
3.3 FacebookPages
Table Name: facebook_pages
Service: complyai-core
Description: Facebook Pages for ad pre-check feature
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 50 |
user_id | INTEGER | No | FK to users | 42 |
page_name | VARCHAR(255) | No | Page name | Acme Official |
profile_photo_url | VARCHAR(512) | No | Profile pic URL | https://... |
cover_photo_url | VARCHAR(512) | No | Cover photo URL | https://... |
website | VARCHAR(255) | Yes | Page website | https://acme.com |
status | VARCHAR(50) | No | Verification status | verified |
task_id | VARCHAR(255) | Yes | Async task ID | celery-task-123 |
message | TEXT | Yes | Status message | Processing... |
page_id | VARCHAR(255) | Yes | Facebook page ID | 111222333 |
created_time | TIMESTAMP | No | Record creation | 2024-05-01 12:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-05-01 12:30:00 |
4. Compliance Domain
4.1 FacebookAdStatus
Table Name: facebook_ad_status
Service: complyai-core
Description: ComplyAI internal status tracking for ads
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 20000 |
ad_id | VARCHAR(50) | No | Facebook ad ID | 23849203842934 |
status | VARCHAR(50) | Yes | ComplyAI status | Open |
false_positive | BOOLEAN | No | Marked as false positive | false |
created_time | TIMESTAMP | No | Record creation | 2024-06-01 10:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-25 14:00:00 |
ComplyAI Status Values:
| Status | Description |
|---|---|
Open | New disapproved ad, needs attention |
Edit Required | Changes needed before resubmission |
Appealed | Appeal submitted to Meta |
Appeal Won | Appeal successful, ad approved |
Appeal Lost | Appeal rejected |
Rejected Archive | Archived rejected ad |
Ignored | Marked to ignore |
4.2 FacebookTotalAdDataFeedback
Table Name: facebook_total_ad_data_feedback
Service: complyai-core
Description: Admin and client feedback on ad reviews
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 300 |
ad_id | VARCHAR(50) | No | Facebook ad ID | 23849203842934 |
admin_id | INTEGER | Yes | FK to admin user | 5 |
admin_response | TEXT | Yes | Admin feedback text | Recommend changing... |
admin_response_time | TIMESTAMP | Yes | When admin responded | 2024-06-20 09:00:00 |
client_id | INTEGER | Yes | FK to client user | 42 |
client_response | TEXT | Yes | Client response | Thanks, will update |
client_response_time | TIMESTAMP | Yes | When client responded | 2024-06-20 10:30:00 |
admin_read | BOOLEAN | No | Admin read client response | true |
client_read | BOOLEAN | No | Client read admin feedback | true |
created_time | TIMESTAMP | No | Record creation | 2024-06-20 08:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-20 10:30:00 |
4.3 AdAccountCase
Table Name: ad_account_case
Service: complyai-core
Description: Meta support case tracking
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 75 |
ad_account_id | VARCHAR(50) | No | Ad account ID | 123456789 |
case_id | VARCHAR(50) | No | Meta case number | CS-123456 |
case_contact | VARCHAR(50) | No | Meta rep contact | meta-support@fb.com |
5. Operational Domain
5.1 ActivityEvents
Table Name: activity_events
Service: complyai-core
Description: Audit log of user and system actions
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 100000 |
user_id | INTEGER | Yes | FK to users | 42 |
ip_address | VARCHAR(64) | Yes | Client IP address | 192.168.1.1 |
action | VARCHAR(100) | Yes | Action type/code | AD_STATUS_CHANGED |
description | TEXT | Yes | Detailed description | Ad 123 changed from... |
created_time | TIMESTAMP | No | Event timestamp | 2024-06-25 14:30:00 |
Common Action Types:
| Code | Description |
|---|---|
| 1 | User Login |
| 2 | User Logout |
| 10 | Ad Submitted |
| 11 | Ad Approved |
| 12 | Ad Rejected |
| 20 | Appeal Submitted |
| 21 | Appeal Won |
| 22 | Appeal Lost |
| 26 | Ad Status Changed |
| 27 | Case ID Added |
| 28 | Case ID Updated |
| 29 | Case Contact Added |
| 30 | Case Contact Updated |
5.2 Notifications
Table Name: notifications
Service: complyai-core
Description: System notifications for organizations
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 500 |
title | VARCHAR(300) | Yes | Notification title | Appeal Won! |
notification_type | VARCHAR(100) | Yes | Type category | appeal_result |
content | TEXT | Yes | Full message content | Your ad 123 got... |
read | BOOLEAN | No | Read status | false |
organization_id | INTEGER | Yes | FK to organizations | 15 |
created_time | TIMESTAMP | No | Creation time | 2024-06-25 10:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-25 10:00:00 |
5.3 Issues
Table Name: issues
Service: complyai-core
Description: Support tickets and feedback
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 25 |
user_id | INTEGER | Yes | FK to users | 42 |
name | VARCHAR(120) | Yes | Reporter name | Jane Smith |
email | TEXT | Yes | Contact email | jane@company.com |
company_id | INTEGER | Yes | FK to organizations | 15 |
issue | TEXT | Yes | Issue description | Cannot connect... |
issue_type | VARCHAR(100) | Yes | Category | bug_report |
created_time | TIMESTAMP | No | Submitted time | 2024-06-20 11:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-20 11:00:00 |
5.4 InviteCodes
Table Name: invite_codes
Service: complyai-core
Description: User invitation codes for onboarding
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
id | INTEGER | No | Primary key | 100 |
invite_code | VARCHAR(20) | No | Unique invite code | ABC123XYZ |
company_id | INTEGER | No | Target organization ID | 15 |
email | VARCHAR(255) | No | Invited email | newuser@company.com |
role | VARCHAR(50) | No | Assigned role | client |
used | BOOLEAN | No | Code used status | false |
created_time | TIMESTAMP | No | Code creation | 2024-06-01 09:00:00 |
updated_time | TIMESTAMP | No | Last update | 2024-06-01 09:00:00 |
6. Junction Tables
6.1 user_organizations
Table Name: user_organizations
Description: Many-to-many relationship between users and organizations
| Field | Type | Description |
|---|---|---|
user_id | INTEGER | FK to users |
org_id | INTEGER | FK to organizations |
6.2 roles_users
Table Name: roles_users
Description: Flask-Security role assignments
| Field | Type | Description |
|---|---|---|
user_id | INTEGER | FK to users |
role_id | INTEGER | FK to roles |
6.3 PagePartners
Table Name: page_partners
Description: Associates users as partners on Facebook Pages
| Field | Type | Description | Example |
|---|---|---|---|
page_id | INTEGER | FK to facebook_pages | 50 |
user_id | INTEGER | FK to users | 42 |
status | VARCHAR(50) | Partnership status | approved |
created_at | TIMESTAMP | Creation time | 2024-05-15 10:00:00 |
📝 Changelog
| Date | Version | Changes |
|---|---|---|
| 2024-12 | 1.0.0 | Initial data dictionary created |
👥 Ownership
| Domain | Data Steward | Technical Owner |
|---|---|---|
| Customer | Product Team | Core API Team |
| Ad Account | Product Team | API Team |
| Ad Content | Product Team | API Team |
| Compliance | Compliance Team | Core API Team |
| Operational | Engineering | Platform Team |