Database Technical Reference
This guide provides a comprehensive breakdown of the PostgreSQL schema used by PlugZero Analytics. It details every table, foreign key relationship, and field type.
🏗️ Organizational Domain (accounts)
This layer manages multi-tenancy, user identity, and team-based access control.
1. User Profile (User)
Extends Django’s AbstractUser. Handles authentication and tier-based segmentation.
| Field | Type | Key | Description |
|---|---|---|---|
id | BigInt | PK | Internal unique identifier. |
email | VarChar(255) | Unique | The primary login identifier. |
segment | VarChar(20) | - | User tier: STUDENT, SMB, ENTERPRISE. |
institution | VarChar(255) | - | University or company name. |
is_2fa_enabled | Boolean | - | Security flag for multi-factor status. |
totp_secret | VarChar(255) | - | Encrypted seed for TOTP apps. |
2. Team Workspace (Team)
The root of data ownership.
| Field | Type | Key | Description |
|---|---|---|---|
id | BigInt | PK | Internal unique identifier. |
name | VarChar(255) | - | Display name of the team workspace. |
owner_id | BigInt | FK | Links to User table (Owner). |
created_at | DateTime | - | Date of team creation. |
3. Team Membership (TeamMembership)
Links users to teams with specific permission roles.
| Field | Type | Key | Description |
|---|---|---|---|
user_id | BigInt | FK | Links to User. |
team_id | BigInt | FK | Links to Team. |
role | VarChar(20) | - | Role: OWNER, ADMIN, MEMBER, VIEWER. |
📂 Ingestion & Project Domain (data_ingestion)
Handles raw research assets, surveys, and collection workflows.
1. Research Project (Project)
A container for all research assets within a team.
| Field | Type | Key | Description |
|---|---|---|---|
id | BigInt | PK | Internal unique identifier. |
team_id | BigInt | FK | Links to Team. Projects are siloed by team. |
created_by_id | BigInt | FK | Links to User. |
name | VarChar(255) | - | Project title. |
description | Text | - | Background context for the AI engine. |
2. Raw Asset (RawDataFile)
Stores uploaded CSV, Excel, and PDF files.
| Field | Type | Key | Description |
|---|---|---|---|
project_id | BigInt | FK | Links to Project. |
file | FilePath | - | S3 path to the original object. |
file_type | VarChar(20) | - | TABULAR or UNSTRUCTURED. |
upload_status | VarChar(20) | - | PENDING, SUCCESS, FAILED. |
columns_metadata | JSONB | - | Map of detected column names and types. |
3. Survey Engine (Survey)
The configuration for research collection missions.
| Field | Type | Key | Description |
|---|---|---|---|
uuid | UUID | Unique | Public identifier for survey links. |
project_id | BigInt | FK | Links to Project. |
title | VarChar(255) | - | Public survey title. |
layout_style | VarChar(10) | - | CLASSIC (scroll) or FOCUS (one-by-one). |
theme_config | JSONB | - | UI colors, fonts, and background images. |
🧠 Intelligence & Analytics Domain (analysis, intelligence)
Stores the computed findings and external web data.
1. Analysis Result (AnalysisResult)
The persistent cache for heavy mathematical calculations.
| Field | Type | Key | Description |
|---|---|---|---|
id | BigInt | PK | Internal unique identifier. |
project_id | BigInt | FK | Links to Project. |
calculation_type | VarChar(50) | - | MEAN, CROSSTAB, SENTIMENT, etc. |
parameters | JSONB | - | Variable selections (e.g., Column X vs Column Y). |
result_data | JSONB | - | The final statistical output table. |
2. Research Insight (ResearchInsight)
Atomic findings generated by AI or human analysts.
| Field | Type | Key | Description |
|---|---|---|---|
project_id | BigInt | FK | Links to Project. |
title | VarChar(255) | - | The “Headline” finding. |
ai_generated | Boolean | - | Flag for human vs AI provenance. |
embedding | JSONB | - | Vector representation for semantic search. |
3. SEO Site Audit (SiteAudit)
Head record for technical website scans.
| Field | Type | Key | Description |
|---|---|---|---|
project_id | BigInt | FK | Links to Project. |
target_url | URL | - | The base URL being audited. |
status_codes | JSONB | - | Breakdown of 200 vs 404 vs 500 counts. |
score | Integer | - | Calculated health score (0-100). |
🕸️ Entity Relationship Overview
The diagram below shows the core “Foreign Key” flow of the system:
1. The Multi-Tenant Anchor
Team → Project (All research is isolated by the team_id foreign key).
2. The Data Sources
Project → RawDataFile (Many-to-One)
Project → Survey (Many-to-One)
Project → ScrapeTarget (Many-to-One)
3. The Computation Flow
Project → AnalysisResult (One-to-Many cache)
AnalysisResult → ResearchInsight (Findings are derived from analysis results).
4. The Collaboration Layer
Report → Comment (Anchored collaboration on text ranges).
🛠️ Performance & Constraints
- Indexing Strategy:
- Every Foreign Key has a B-Tree index by default (Django convention).
- Composite indexes are used on
(project_id, created_at)for tables with high time-series traffic (e.g.,ExperienceEvent).
- JSONB Usage: We utilize PostgreSQL JSONB for all flexible metadata. This allows for rapid schema changes without requiring database migrations for every new survey question type.
- Soft Deletion: While we use
on_delete=models.CASCADEfor most child objects, we use logical flags for Users and Teams to prevent accidental mass data loss.
Strict Deployment Rule: Never run ALTER TABLE commands manually in production. All schema changes MUST go through Django Migrations to ensure consistency between the local environment, the build server, and our production RDS instance.