Database Schema (ERD)

Database Schema

The Merq platform uses a relational database schema in PostgreSQL to manage its core entities. The design follows a hierarchical structure, with all data being partitioned by a workspace_id.

Core Domain Hierarchy

The main data flow follows this progression: Principals have Projects → Projects are assigned to Teams → Teams are assigned Outlets → Agents perform Visits to Outlets → Visits generate Submissions.

Entity Relationship Diagram (ERD)

This diagram illustrates the relationships between the most important tables in the database.

erDiagram
    Workspace {
        int id PK
        string name
    }

    User {
        int id PK
        int workspace_id FK
        int role_id FK
        string name
        string email
    }

    Role {
        int id PK
        int workspace_id FK
        string name
    }

    Principal {
        int id PK
        int workspace_id FK
        string name
    }

    Project {
        int id PK
        int workspace_id FK
        int principal_id FK
        string name
    }

    Team {
        int id PK
        int workspace_id FK
        string name
    }

    Outlet {
        int id PK
        int workspace_id FK
        int project_id FK
        string name
        string address
    }

    OutletVisit {
        int id PK
        int workspace_id FK
        int outlet_id FK
        int user_id FK
        datetime check_in_time
        datetime check_out_time
    }

    Product {
        int id PK
        int workspace_id FK
        string name
        string sku_code
    }

    Submission {
        int id PK
        int visit_id FK
        string type
        jsonb data
    }

    TeamUser {
        int team_id PK
        int user_id PK
    }

    ProjectTeam {
        int project_id PK
        int team_id PK
    }

    Workspace ||--o{ User : "has"
    Workspace ||--o{ Role : "has"
    Workspace ||--o{ Principal : "has"
    Workspace ||--o{ Project : "has"
    Workspace ||--o{ Team : "has"
    Workspace ||--o{ Outlet : "has"
    Workspace ||--o{ Product : "has"
    Workspace ||--o{ OutletVisit : "has"

    User }o--|| Role : "has"
    User }o--o{ TeamUser : "belongs to"
    User }o--o{ OutletVisit : "performs"

    Team ||--o{ TeamUser : "has"
    Team ||--o{ ProjectTeam : "assigned to"

    Principal ||--o{ Project : "has"

    Project }o--|| Principal : "belongs to"
    Project }o--o{ ProjectTeam : "has"
    Project }o--o{ Outlet : "contains"

    Outlet }o--|| Project : "belongs to"
    Outlet ||--o{ OutletVisit : "has"

    OutletVisit }o--o{ Submission : "generates"

Key Table Explanations

Workspace: The top-level entity for data isolation. Every other major entity is directly or indirectly associated with a workspace_id. This is the cornerstone of the multi-tenant architecture.
User: Represents an individual user account. Each user belongs to one Workspace and is assigned one Role.
Role: Defines a set of permissions. Roles are workspace-specific.
Principal: Represents a brand or manufacturer whose products are being merchandised.
Project: A specific campaign or initiative for a Principal. This is a central organizing entity.
Team: A group of Users (field agents) who work on projects.
Outlet: A retail store or location that needs to be visited. Outlets are associated with a Project.
OutletVisit: Records a single visit by a User to an Outlet. It captures check-in/check-out times and location data.
Submission: Stores the data collected during an OutletVisit, such as stock counts, photos, or survey answers. The data field is a flexible jsonb column to accommodate different submission types.
Product: Represents a product that is tracked within the system.

Join Tables

TeamUser: A many-to-many join table linking Users to Teams. A user can be part of multiple teams, and a team has multiple users.
ProjectTeam: A many-to-many join table linking Projects to Teams. A project can be assigned to multiple teams, and a team can work on multiple projects.

Additional Entities

These tables exist in the database but are omitted from the ERD above to keep the diagram focused on the core domain hierarchy.

HR & Employee Management

Table	Description
`employee_schedules`	Weekly work schedule per employee (weekday, work_start/end, is_off_day).
`employee_compensations`	Salary metadata per employee (salary_band, pay_type, base_salary, currency). One-to-one with `users`.
`employee_leave_entitlements`	Annual leave allocations per employee per year (annual, sick, carry-over days).
`leave_requests`	Leave/time-off requests with an approval workflow (pending → approved/rejected).

Reimbursements

Table	Description
`reimbursements`	Expense reimbursement claims submitted by employees. Tracks category (transport, meal, fuel, etc.), amount, receipt photo, and an approval workflow (pending → approved/rejected). Optionally linked to an outlet or visit.

Sales Orders

Table	Description
`sales_orders`	Sales order records managed by field agents. See `internal/domain` for the full model.

Form Builder

Table	Description
`forms`	Dynamic forms created via the form builder. Used for custom submission types and surveys during outlet visits.

Plans & Subscriptions

Table	Description
`plans`	Subscription plans (e.g., Free, Basic, Pro).
`plan_types`	Plan type categories (free, basic, pro, enterprise).
`plan_features`	Features that can be assigned to plans (e.g., “Unlimited Projects”).
`plan_feature_assignments`	Many-to-many join between `plans` and `plan_features`.
`plan_subscriptions`	Links a `workspace` to a `plan`.
`plan_pricings`	Pricing rows per plan (amount, currency, interval: monthly/yearly).
`plan_limits`	Resource limits imposed by a plan (e.g., max projects, max users).
`plan_modules`	Modules controlled by plan limits (e.g., analytics, reporting).

Locations & Currency

Table	Description
`locations`	Geographic location records (cities, regions, etc.) used for outlet and user location data.
`currencies`	Currency definitions used across reimbursements, plan pricing, and compensation.

Notifications

Table	Description
`notifications`	In-app and push notification records sent to users.

Operational

Table	Description
`operating_schedules`	Workspace-level operating schedule definitions (e.g., business hours).
`master_settings`	Key-value settings store for workspace-level configuration.
`login_audits`	Audit log of user login events (timestamp, IP, device).
`seeder_histories`	Tracks which seeder scripts have been run to prevent duplicate execution.
`export_jobs`	Async export job records (status, file URL, entity type).
`import_jobs`	Async import job records (status, validation results, entity type).