FluentCRM Database Schema

FluentCRM Core Advanced

FluentCRM use custom database tables to store all the CRM data. Here are the list of database tables and it's schema to understand overall database design and related data attributes of each model.

Schema Design

Database Tables

_fc_subscribers Table

This table store the basic information of a contact

Column	Type	Comment
id	bigint unsigned Auto Increment
user_id	bigint unsigned NULL
hash	varchar(90) NULL
contact_owner	bigint unsigned NULL
company_id	bigint unsigned NULL
prefix	varchar(192) NULL
first_name	varchar(192) NULL
last_name	varchar(192) NULL
email	varchar(190)
timezone	varchar(192) NULL
address_line_1	varchar(192) NULL
address_line_2	varchar(192) NULL
postal_code	varchar(192) NULL
city	varchar(192) NULL
state	varchar(192) NULL
country	varchar(192) NULL
ip	varchar(20) NULL
latitude	decimal(10,8) NULL
longitude	decimal(10,8) NULL
total_points	int unsigned [0]
life_time_value	int unsigned [0]
phone	varchar(50) NULL
status	varchar(50) [subscribed]
contact_type	varchar(50) NULL [lead]
source	varchar(50) NULL
avatar	varchar(192) NULL
date_of_birth	date NULL
created_at	timestamp NULL
last_activity	timestamp NULL
updated_at	timestamp NULL

fc_tags

Storing the tags information

Column	Type	Comment
id	int unsigned Auto Increment
title	varchar(192)
slug	varchar(192)
description	tinytext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

fc_lists

Storing the lists information

Column	Type	Comment
id	int unsigned Auto Increment
title	varchar(192)
slug	varchar(192)
description	tinytext NULL
is_public	tinyint(1) NULL [0]
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_subscriber_pivot

Pivot Table for subscriber's tag and list relationship

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
object_id	bigint unsigned
object_type	varchar(50)
status	varchar(50) NULL
is_public	tinyint(1) [1]
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_subscriber_meta

Meta table for subscribers

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
created_by	bigint unsigned
object_type	varchar(50) NULL [option]
key	varchar(192)
value	longtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_subscriber_notes

Subscriber's Note table

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
parent_id	bigint unsigned NULL
created_by	bigint unsigned NULL
status	varchar(50) NULL [open]
type	varchar(50) NULL [note]
is_private	tinyint NULL [1]
title	varchar(192) NULL
description	longtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_subscriber_notes

Subscriber's Note table

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
parent_id	bigint unsigned NULL
created_by	bigint unsigned NULL
status	varchar(50) NULL [open]
type	varchar(50) NULL [note]
is_private	tinyint NULL [1]
title	varchar(192) NULL
description	longtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_campaigns

Campaigns Table. This table store email campaigns, sequence emails, email action from automation

Column	Type	Comment
id	bigint unsigned Auto Increment
parent_id	bigint unsigned NULL
type	varchar(50) [campaign]
title	varchar(192)
available_urls	text NULL
slug	varchar(192)
status	varchar(50)
template_id	bigint unsigned NULL
email_subject	varchar(192) NULL
email_pre_header	varchar(192) NULL
email_body	longtext
recipients_count	int [0]
delay	int NULL [0]
utm_status	tinyint(1) NULL [0]
utm_source	varchar(192) NULL
utm_medium	varchar(192) NULL
utm_campaign	varchar(192) NULL
utm_term	varchar(192) NULL
utm_content	varchar(192) NULL
design_template	varchar(192) NULL
scheduled_at	timestamp NULL
settings	longtext NULL
created_by	bigint unsigned NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_campaign_emails

Store individual emails of a campaign

Column	Type	Comment
id	bigint unsigned Auto Increment
campaign_id	bigint unsigned NULL
email_type	varchar(50) NULL [campaign]
subscriber_id	bigint unsigned NULL
email_subject_id	bigint unsigned NULL
email_address	varchar(192)
email_subject	varchar(192) NULL
email_body	longtext NULL
email_headers	text NULL
is_open	tinyint(1) [0]
is_parsed	tinyint(1) [0]
click_counter	int NULL
status	varchar(50) [draft]
note	text NULL
scheduled_at	timestamp NULL
email_hash	varchar(192) NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_campaign_url_metrics

Email Open/Click Tracking Table

Column	Type	Comment
id	bigint unsigned Auto Increment
url_id	bigint unsigned NULL
campaign_id	bigint unsigned NULL
subscriber_id	bigint unsigned NULL
type	varchar(50) NULL [click]
ip_address	varchar(30) NULL
country	varchar(40) NULL
city	varchar(40) NULL
counter	int unsigned [1]
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_sequence_tracker

Tracking Database for Email Sequences

Column	Type	Comment
id	bigint unsigned Auto Increment
campaign_id	bigint unsigned NULL
last_sequence_id	bigint unsigned NULL
subscriber_id	bigint unsigned NULL
next_sequence_id	bigint unsigned NULL
status	varchar(50) NULL [active]
type	varchar(50) NULL [sequence_tracker]
last_executed_time	timestamp NULL
next_execution_time	timestamp NULL
notes	text NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_funnels

Automation / Funnel Storage Table

Column	Type	Comment
id	bigint unsigned Auto Increment
type	varchar(50) [funnel]
title	varchar(192)
trigger_name	varchar(150) NULL
status	varchar(50) NULL [draft]
conditions	text NULL
settings	text NULL
created_by	bigint unsigned NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_funnel_sequences

Automation / Funnel Sequences Storage Table

Column	Type	Comment
id	bigint unsigned Auto Increment
funnel_id	bigint unsigned NULL
parent_id	bigint unsigned NULL [0]
action_name	varchar(192) NULL
condition_type	varchar(192) NULL
type	varchar(50) NULL [sequence]
title	varchar(192) NULL
description	varchar(192) NULL
status	varchar(50) NULL [draft]
conditions	text NULL
settings	text NULL
note	text NULL
delay	int unsigned NULL
c_delay	int unsigned NULL
sequence	int unsigned NULL
created_by	bigint unsigned NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_funnel_subscribers

Funnel Sequence - Funnel - Subscriber Relationship DB Table

Column	Type	Comment
id	bigint unsigned Auto Increment
funnel_id	bigint unsigned NULL
starting_sequence_id	bigint unsigned NULL
next_sequence	bigint unsigned NULL
subscriber_id	bigint unsigned NULL
last_sequence_id	bigint unsigned NULL
next_sequence_id	bigint unsigned NULL
last_sequence_status	varchar(50) NULL [pending]
status	varchar(50) NULL [active]
type	varchar(50) NULL [funnel]
last_executed_time	timestamp NULL
next_execution_time	timestamp NULL
notes	text NULL
source_trigger_name	varchar(192) NULL
source_ref_id	bigint unsigned NULL
created_at	timestamp NULL
updated_at	timestamp NULL

_fc_funnel_metrics

Funnel Sequence - Tracking Table for a subscriber

Column	Type	Comment
id	bigint unsigned Auto Increment
funnel_id	bigint unsigned NULL
sequence_id	bigint unsigned NULL
subscriber_id	bigint unsigned NULL
benchmark_value	bigint unsigned NULL [0]
benchmark_currency	varchar(10) NULL [USD]
status	varchar(50) NULL [completed]
notes	text NULL
created_at	timestamp NULL
updated_at	timestamp NULL

fc_contact_relations

This table will be available for extended ecommerce module if you sync the data from Ecommerce / LMS

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
provider	varchar(100)
provider_id	bigint unsigned NULL
first_order_date	timestamp NULL
last_order_date	timestamp NULL
total_order_count	int NULL [0]
total_order_value	decimal(10,2) NULL [0.00]
status	varchar(100) NULL
commerce_taxonomies	longtext NULL
commerce_coupons	longtext NULL
meta_col_1	mediumtext NULL
meta_col_2	mediumtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

fc_contact_relation_items

This table will be available for extended ecommerce module if you sync the data from Ecommerce / LMS for storing individual record for ecommerce / LMS contacts

Column	Type	Comment
id	bigint unsigned Auto Increment
subscriber_id	bigint unsigned
relation_id	bigint unsigned
provider	varchar(100)
origin_id	bigint unsigned NULL
item_id	bigint unsigned
item_sub_id	bigint unsigned NULL
item_value	decimal(10,2) NULL
status	varchar(100) NULL
item_type	varchar(100) NULL
meta_col	mediumtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL

fc_smart_links

For storing SmartLinks and it's configuration

Column	Type	Comment
id	bigint unsigned Auto Increment
title	varchar(192) NULL
short	varchar(192) NULL
target_url	text NULL
actions	text NULL
notes	text NULL
contact_clicks	int NULL [0]
all_clicks	int NULL [0]
created_by	bigint unsigned NULL
created_at	timestamp NULL
updated_at	timestamp NULL

fc_url_stores

For storing Email Long Links and short links for tracking

Column	Type	Comment
id	bigint unsigned Auto Increment
url	tinytext
short	varchar(50)
created_at	timestamp NULL
updated_at	timestamp NULL

fc_meta

For storing CRM settings

Column	Type	Comment
id	bigint unsigned Auto Increment
object_type	varchar(50)
object_id	bigint NULL
key	varchar(192)
value	longtext NULL
created_at	timestamp NULL
updated_at	timestamp NULL