Skip to content
M
Merq

Database Schema

Database Schema

Section titled “Database Schema”

This document outlines the database schema for the Merq Field Force Management Platform. The database uses PostgreSQL with GORM as the ORM.

Overview

Section titled “Overview”
  • Database: PostgreSQL
  • ORM: GORM
  • Multi-Tenant: Each workspace has isolated data with workspace_id foreign key

Core Tables

Section titled “Core Tables”

1. Workspaces

Section titled “1. Workspaces”
workspace.go
type Workspace struct {
    ID          uint
    Name        string
    slug        string
    Logo        string
    PlanID      uint
    Status      uint16  // 1=active, 2=inactive, 3=suspended
    CreatedAt   time.Time
    UpdatedAt   time.Time
}


// Indexes
- PRIMARY KEY (id)
- UNIQUE INDEX on slug
- INDEX on status

2. Users

Section titled “2. Users”
user.go
type User struct {
    ID            uint
    Email         string
    Phone         string
    Password      string
    RoleID        uint
    WorkspaceID   uint
    Status        uint16  // 1=active, 2=inactive
    LastLoginAt   *time.Time
    CreatedAt     time.Time
    UpdatedAt     time.Time


    // Relations
    Role          *Role
    UserDetail    *UserDetail
    Workspace     *Workspace
}


// Indexes
- PRIMARY KEY (id)
- UNIQUE INDEX on email + workspace_id
- INDEX on workspace_id
- INDEX on role_id

3. User Details

Section titled “3. User Details”
user_detail.go
type UserDetail struct {
    ID              uint
    UserID          uint
    Name            string
    PhotoUrl        string
    Phone           string
    Address         string
    JoinDate        *time.Time
    FCMToken        string  // Firebase Cloud Messaging token
    // ... additional fields
}


// Indexes
- PRIMARY KEY (id)
- INDEX on user_id

4. Roles

Section titled “4. Roles”
role.go
type Role struct {
    ID          uint
    Name        string
    Description string
    Platform    string  // admin, mobile, web
    Permissions string  // JSON array of permission keys
    IsDefault   bool
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id

5. Permissions

Section titled “5. Permissions”
permission.go
type Permission struct {
    ID          uint
    Key         string  // e.g., "visit.create", "outlet.view"
    Name        string
    Description string
    Platform    string
    Module      string
}


// Indexes
- PRIMARY KEY (id)
- UNIQUE INDEX on key + platform

6. Outlets

Section titled “6. Outlets”
outlet.go
type Outlet struct {
    OutletID        uint   // PRIMARY KEY (uses outlet_id column)
    OutletRegionID  uint
    OutletTypeID    uint
    Name            string
    PhotoUrl        string
    Address         string
    GeoLat          float64
    GeoLng          float64
    RadiusM         float64
    OpeningHours    string
    Frequency       int     // Stay/Regular/F1-F4
    Status          uint16  // 1=active, 2=paused, 3=closed
    ManagerName     string
    ManagerPhone    string
    ManagerEmail    string
    Tags            string
    WorkspaceID     uint
    CreatedAt       time.Time
    UpdatedAt       time.Time


    // Relations (many2many)
    Projects        []Project
    Teams           []Team
    Forms            []Form
    Products         []Product
}


// Indexes
- PRIMARY KEY (outlet_id)
- INDEX on workspace_id
- INDEX on outlet_region_id
- INDEX on outlet_type_id
- INDEX on status
- SPATIAL INDEX on (geo_lat, geo_lng) for location queries

7. Outlet Regions

Section titled “7. Outlet Regions”
// Domain: common.go (OutletRegion)
type OutletRegion struct {
    ID          uint
    Name        string
    Description string
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

8. Outlet Types

Section titled “8. Outlet Types”
// Domain: common.go (OutletType)
type OutletType struct {
    ID          uint
    Name        string
    Description string
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

9. Projects

Section titled “9. Projects”
project.go
type Project struct {
    ProjectID    uint
    PrincipalID  uint
    Name         string
    Description  string
    StartDate    *time.Time
    EndDate      *time.Time
    Status       uint16  // 1=active, 2=completed, 3=cancelled
    WorkspaceID  uint
    CreatedAt    time.Time
    UpdatedAt    time.Time


    // Relations
    Principal    *Principal
}


// Indexes
- PRIMARY KEY (project_id)
- INDEX on workspace_id
- INDEX on principal_id
- INDEX on status

10. Teams

Section titled “10. Teams”
team.go
type Team struct {
    TeamID       uint
    Name         string
    Description  string
    LeaderID     uint
    WorkspaceID  uint
    Status       uint16
    CreatedAt    time.Time
    UpdatedAt    time.Time


    // Relations
    Leader       *User
}


// Indexes
- PRIMARY KEY (team_id)
- INDEX on workspace_id
- INDEX on leader_id

11. Visits (Outlet Visits)

Section titled “11. Visits (Outlet Visits)”
outlet_visit.go
type OutletVisit struct {
    ID              uint
    OutletID        uint
    ProjectID       uint
    TeamID          uint
    AssignedBy      uint
    ScheduledAt     time.Time
    Status          string  // not_started, in_progress, completed, cancelled
    Title           string
    Description     string
    Notes           string
    DurationSecond  *int64
    ComplianceScore *float64
    RouteName       *string   // Route grouping
    RouteDay        *int      // Day sequence
    Sequence        *int      // Stop sequence
    EstimatedTime   *string   // HH:MM format
    WorkspaceID     uint
    CreatedAt       time.Time
    UpdatedAt       time.Time


    // Relations
    Outlet          Outlet
    Project         Project
    Team            Team
    Assignees       []OutletVisitAssignee
    Submissions     []OutletVisitSubmission
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id
- INDEX on outlet_id
- INDEX on project_id
- INDEX on team_id
- INDEX on scheduled_at
- INDEX on status
- INDEX on route_name + route_day + sequence

12. Visit Assignees

Section titled “12. Visit Assignees”
outlet_visit.go
type OutletVisitAssignee struct {
    ID            uint
    OutletVisitID  uint
    UserID        uint
    Status        string  // pending, in_progress, checked_in, checked_out
    CheckedInAt   *time.Time
    CheckedOutAt   *time.Time
    CreatedAt      time.Time
    UpdatedAt      time.Time


    // Relations
    User          *User
}


// Indexes
- PRIMARY KEY (id)
- INDEX on outlet_visit_id
- INDEX on user_id

13. Visit Submissions

Section titled “13. Visit Submissions”
outlet_visit.go
type OutletVisitSubmission struct {
    ID              uint
    OutletVisitID   *uint
    FormID          uint
    UserID          uint
    Title           string
    Status          string  // pending, completed, skipped
    Answers         string  // JSON
    PhotoUrl        *string
    ApprovalStatus  string  // pending, approved, rejected
    WorkspaceID     uint
    CreatedAt       time.Time
    UpdatedAt       time.Time


    // Relations
    Form            Form
}


// Indexes
- PRIMARY KEY (id)
- INDEX on outlet_visit_id
- INDEX on form_id
- INDEX on user_id
- INDEX on workspace_id

14. Forms (Form Builder)

Section titled “14. Forms (Form Builder)”
form.go
type Form struct {
    ID          uint
    Name        string
    Description string
    Type        string  // visit, general
    Status      string  // active, inactive
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time


    // Relations
    Fields      []FormField
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id
- INDEX on status

15. Form Fields

Section titled “15. Form Fields”
form.go
type FormField struct {
    ID          uint
    FormID      uint
    Name        string
    Label       string
    Type        string  // text, number, photo, signature, etc.
    Order       int
    IsRequired  bool
    Options     string  // JSON for select/radio options
    CreatedAt   time.Time
    UpdatedAt   time.Time
}


// Indexes
- PRIMARY KEY (id)
- INDEX on form_id

16. Products

Section titled “16. Products”
product.go
type Product struct {
    ProductID    uint
    PrincipalID  uint
    CategoryID   uint
    Name         string
    Description  string
    PhotoUrl     string
    SKU          string
    Status       uint16
    WorkspaceID  uint
    CreatedAt    time.Time
    UpdatedAt    time.Time
}


// Indexes
- PRIMARY KEY (product_id)
- INDEX on workspace_id
- INDEX on principal_id
- INDEX on category_id

17. Principals

Section titled “17. Principals”
principal.go
type Principal struct {
    ID          uint
    Name        string
    Description string
    LogoUrl     string
    Status      uint16
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id

18. Attendance

Section titled “18. Attendance”
atendance.go
type Attendance struct {
    ID          uint
    UserID      uint
    TeamID      uint
    Date        time.Time
    CheckIn     *time.Time
    CheckOut    *time.Time
    Status      string  // present, absent, leave
    Latitude    float64
    Longitude   float64
    PhotoUrl    string
    Notes       string
    WorkspaceID uint
    CreatedAt   time.Time
    UpdatedAt   time.Time


    // Relations
    User        User
    Team        Team
}


// Indexes
- PRIMARY KEY (id)
- INDEX on user_id + date
- INDEX on workspace_id

19. Notifications

Section titled “19. Notifications”
notification.go
type Notification struct {
    ID          uint
    WorkspaceID uint
    UserID      uint
    Title       string
    Message     string
    Type        string  // submission_created, visit_assigned, etc.
    EntityType  string  // submission, visit, outlet
    EntityID    *uint
    IsRead      bool
    ReadAt      *time.Time
    CreatedAt   time.Time


    // Relations
    User        User
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id + user_id
- INDEX on user_id + is_read
- INDEX on created_at

20. Notification Tokens

Section titled “20. Notification Tokens”
notification.go
type NotificationToken struct {
    ID          uint
    UserID      uint
    WorkspaceID uint
    Token       string  // FCM device token
    DeviceType  string  // ios, android, web
    DeviceID    string
    CreatedAt   time.Time


    // Relations
    User        User
}


// Indexes
- PRIMARY KEY (id)
- UNIQUE INDEX on token
- INDEX on user_id + workspace_id

21. Sales Orders

Section titled “21. Sales Orders”
// Domain: common.go (SalesOrder)
type SalesOrder struct {
    ID              uint
    OutletID        uint
    UserID          uint
    OrderNumber     string
    TotalAmount     float64
    Status          string  // pending, confirmed, shipped, delivered
    Notes           string
    WorkspaceID     uint
    CreatedAt       time.Time
    UpdatedAt       time.Time


    // Relations
    Outlet          Outlet
    User            User
    Items           []SalesOrderItem
}


// Indexes
- PRIMARY KEY (id)
- INDEX on workspace_id
- INDEX on outlet_id
- INDEX on user_id

Workspace Isolation Strategy

Section titled “Workspace Isolation Strategy”

All tables that contain workspace-specific data include:

workspace_id uint NOT NULL

Queries MUST always include WHERE workspace_id = ? to ensure data isolation between workspaces.

Common Indexes

Section titled “Common Indexes”
TableIndexPurpose
usersworkspace_id + emailLogin lookup
outletsworkspace_id + statusList active outlets
outlet_visitsworkspace_id + scheduled_atVisit calendar
outlet_visitsworkspace_id + route_name + route_dayRoute queries
notificationsuser_id + is_readUnread count
Section titled “Related Documentation”