.png?fit=max&auto=format&n=Frm2GFbmok4D-yJA&q=85&s=93c3ebd47542af65d1cd06d8563a7f6e)
Overview
The Mission Control database is organized into three core models that handle the full data pipeline from event ingestion through customer identity resolution and user management.Event Model
Data from every connected client is processed through Kafka and stored in the Events table. The tracking tag is responsible for fetching client click data and sending it to Kafka consumers.Data Flow
Tables
Events
Events
Raw event data ingested through Kafka. This data is further processed by the “Process Data” script for evaluating, analyzing, and scrubbing.
| Column | Type | Constraints |
|---|---|---|
event_id (PK) | int | NOT NULL |
app_id | int | NOT NULL |
platform | char | |
event | char | |
event_id | char |
User Session
User Session
Contains processed user details including orders, visits, and transaction details.
| Column | Type | Constraints |
|---|---|---|
parent_id (PK) | int | NOT NULL |
client_id | int | NOT NULL |
session_id | int | NOT NULL |
event_type | char | |
event_date | timestamp | |
user_journey | ||
user_ipaddress | char |
Order Credit
Order Credit
Stores credit given to each source that a user has come through. Tracks orders and visits by customer.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
session_id (FK) | int | NOT NULL |
source_credit | char | |
event_date | timestamp | |
client_id | int | NOT NULL |
parent_id | char | NOT NULL |
Interaction Insight Summary
Interaction Insight Summary
Records order information by media source for the Customer Interaction platform.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
client_id | int | NOT NULL |
event_date | date | NOT NULL |
media_source | char |
Interaction Insight DateAggSummary
Interaction Insight DateAggSummary
Tracks the number of times users visit a specific website, aggregated by date.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
client_id | int | NOT NULL |
event_date | date | NOT NULL |
media_source | char | |
session_count | numeric |
Customer Model
This model retains data from third-party sources like Shopify and HubSpot. The Data Collector script processes data from these sources and stores it in the Integration Customer table.Data Flow
Tables
Integration Customers
Integration Customers
Raw customer data from third-party integrations.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
client_id | int | NOT NULL |
source_type | char | |
created_at | timestamp |
Configuration Table
Configuration Table
Manages the mapping and routing of customer data from the Integration Customers table to the eventapp tables. Divided into three sub-tables:eventapp_dataobjectconfig
eventapp_idtype
eventapp_attribute
| Column | Type |
|---|---|
id_type | int |
source | char |
data_feed | char |
page_data_type | char |
unique_name | char |
mask_display | bool |
client_id | int |
| Column | Type |
|---|---|
id | int |
name | char |
customer | bool |
belonging_level | int |
recognized | bool |
client_id | int |
| Column | Type |
|---|---|
id_type | int |
name | char |
display_order | int |
label | char |
group_display_order | int |
client_id | int |
eventapp_identity
eventapp_identity
Stores customer credentials (email, customer ID) resolved from the Integration Customers table.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
parent_id (FK) | char | NOT NULL |
child_id | char | NOT NULL |
source | char | |
client_id | int | NOT NULL |
eventapp_userattribute
eventapp_userattribute
Retains additional customer details such as name, address, and contact information.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
alt_id | int | |
client_id | char | |
sort | bool |
eventapp_userevents
eventapp_userevents
Records customer events (currently stores Gorgias data).
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
parent_id (FK) | char | NOT NULL |
event_id | char | |
source | char | |
client_id | char | NOT NULL |
event_details | json |
Users Model
Handles client onboarding and authentication. When a new client registers through the client registration form, their details are stored in theuseraccount_client table with a corresponding client_id. Login credentials (email and password) are stored in the users table under the same client_id.
Tables
useraccount_client
useraccount_client
Client account details created during registration.
| Column | Type |
|---|---|
company | char |
web_url | char |
icon | char |
users
users
User authentication and profile data.
| Column | Type | Constraints |
|---|---|---|
id (PK) | int | NOT NULL |
password | int | |
last_login | int | |
first_name | char | |
last_name | char | |
email | char | |
client_id | int |