Database Design

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

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

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

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

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

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

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

Manages the mapping and routing of customer data from the Integration Customers table to the eventapp tables. Divided into three sub-tables:eventapp_dataobjectconfig

Column	Type
`id_type`	int
`source`	char
`data_feed`	char
`page_data_type`	char
`unique_name`	char
`mask_display`	bool
`client_id`	int

eventapp_idtype

Column	Type
`id`	int
`name`	char
`customer`	bool
`belonging_level`	int
`recognized`	bool
`client_id`	int

eventapp_attribute

Column	Type
`id_type`	int
`name`	char
`display_order`	int
`label`	char
`group_display_order`	int
`client_id`	int

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

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

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 the useraccount_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

Client account details created during registration.

Column	Type
`company`	char
`web_url`	char
`icon`	char

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

Getting Started

Onboarding

Platform

Configuration

AI Agents

Agent Surface

Creative Analytics

Development

Lunar Interns

Performance Reports

Attribution & Reporting

Notes / PMT

Overview

Event Model

Data Flow

Tables

Customer Model

Data Flow

Tables

Users Model

Tables

​Overview

​Event Model

​Data Flow

​Tables

​Customer Model

​Data Flow

​Tables

​Users Model

​Tables

Overview

Event Model

Data Flow

Tables

Customer Model

Data Flow

Tables

Users Model

Tables