Entity Relationship Explained: Zixly Internal Operations

Version: 2.1 Last Updated: 2025-01-27 Owner: Business Architecture Status: Internal Operations Guide

BUSINESS MODEL CLARIFICATION

Zixly is an open-source internal operations platform for the Zixly service business.

This platform:

Zixly is NOT a multi-tenant SaaS platform for external customers.

Table of Contents

  1. Quick Summary
  2. Detailed Explanation
  3. Visual Hierarchy
  4. Real-World Scenario
  5. FAQ: Common Confusions
  6. Technical Implementation

Quick Summary

Three-Second Version:

Business Model: Zixly is a service business that uses its own platform to track service delivery operations, demonstrating “eating our own dogfood” with the self-hostable SME stack.

What This Means:

  1. Single Tenant: Only Zixly organization data
  2. Internal Users: Only Zixly team members
  3. Service Clients: Businesses that hire Zixly for pipeline services automation services
  4. Open-Source: Code available for demonstration and reuse
  5. Dogfooding: Using our own tools to run our business

Detailed Explanation

1. Tenant (Zixly Organization)

Definition: The Tenant represents the Zixly service business itself.

Business Context:

Database Entity: Tenant table

Examples:

Lifecycle:

2. User (Zixly Team Members)

Definition: A User is a Zixly team member who has access to the internal operations platform.

Business Context:

Database Entity: User table

Examples (Zixly team members):

User Roles:

Lifecycle:

3. Client (The Business Entity or Project Being Tracked)

Definition: A Client (technically ClientKPI in the database) is a business entity, customer, or project that the tenant company is tracking performance metrics for.

Business Context:

Database Entity: ClientKPI table

Examples (within ABC Construction’s account):

Associated Data:

Lifecycle:

Visual Hierarchy

┌────────────────────────────────────────────────────────────────┐
│                      ZIXLY PLATFORM                     │
│                         (The Product)                          │
└────────────────────────────────────────────────────────────────┘
                                │
                ┌───────────────┴───────────────┐
                │                               │
                ▼                               ▼
    ┌────────────────────┐          ┌────────────────────┐
    │  TENANT #1         │          │  TENANT #2         │
    │  ABC Construction  │          │  Sydney Property   │
    │  (The Customer)    │          │  (The Customer)    │
    └────────────────────┘          └────────────────────┘
                │                               │
    ┌───────────┴────────────┐                 │
    │                        │                 │
    ▼                        ▼                 ▼
┌──────────┐          ┌──────────┐      ┌──────────┐
│ USER #1  │          │ USER #2  │      │ USER #3  │
│ Sarah    │          │ John     │      │ Michael  │
│ (Admin)  │          │ (Editor) │      │ (Admin)  │
└──────────┘          └──────────┘      └──────────┘
                │
    ┌───────────┴────────────┐
    │                        │
    ▼                        ▼
┌──────────┐          ┌──────────┐
│ CLIENT 1 │          │ CLIENT 2 │
│ Harbor   │          │ Bondi    │
│ Bridge   │          │ Resi Dev │
│ (Project)│          │ (Project)│
└──────────┘          └──────────┘
    │                        │
    ▼                        ▼
┌──────────┐          ┌──────────┐
│Financials│          │Financials│
│LeadEvents│          │LeadEvents│
│CustomKPIs│          │CustomKPIs│
└──────────┘          └──────────┘

Cardinalities:

Data Isolation: Each tenant’s data is completely isolated via PostgreSQL Row-Level Security (RLS). Sarah (ABC Construction) can NEVER see Michael’s data (Sydney Property), even if they query the same database tables.

Real-World Scenario

Zixly Service Business (Tenant)

Context: Zixly is a service business that provides pipeline services automation services to clients. This platform tracks Zixly’s internal service delivery operations.

Tenant Details:

Users (Zixly Team Members)

1. Cole Morton - Founder (ADMIN)

2. Support Team - Operations (EDITOR)

Clients (Zixly’s Service Clients)

Client 1: Harbor Bridge Construction Project

Client 2: Bondi E-commerce Store

Client 3: Sydney Law Firm

User Workflows

Workflow 1: Cole (ADMIN) adds Support Team (EDITOR)

  1. Cole logs into Zixly (authenticated as zixly-org-001)
  2. Navigates to “Team Management” → “Invite User”
  3. Enters support email: support@zixly.com.au
  4. Assigns role: EDITOR
  5. System creates User record: 
    {
  id: "user_002",
  tenantId: "zixly-org-001",  // Scoped to Zixly organization
  email: "support@zixly.com.au",
  role: "EDITOR",
}
  6. Support team receives invitation email and sets password

Workflow 2: Support Team (EDITOR) adds a new service client

  1. Support team logs into Zixly (authenticated as zixly-org-001)
  2. Navigates to “Service Clients” → “Add Client”
  3. Enters details:
    • Client Name: “Melbourne Accounting Firm”
    • External ID: MAF-2024-004
    • Industry: “Professional Services”
    • Currency: “AUD”
  4. System creates ClientKPI record: 
    {
  id: "ckpi_004",
  tenantId: "zixly-org-001",  // Scoped to Zixly organization
  clientId: "MAF-2024-004",
  clientName: "Melbourne Accounting Firm",
  industry: "Professional Services",
  currency: "AUD",
}
  5. System syncs financial data from Xero integration (if configured)

Workflow 3: Cole (ADMIN) generates service delivery profitability report

  1. Cole logs into Zixly (authenticated as zixly-org-001)
  2. Navigates to “Reports” → “Service Delivery Profitability”
  3. Selects date range: Q1 2024 (Jan 1 - Mar 31)
  4. System queries financials: 
    SELECT ck.client_name, SUM(f.revenue) as total_revenue, SUM(f.expenses) as total_expenses
FROM client_kpis ck
JOIN financials f ON ck.id = f.client_kpi_id
WHERE ck.tenant_id = 'zixly-org-001'  -- RLS enforces this automatically
  AND f.record_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY ck.client_name;
  5. Cole views report showing:
    • Harbor Bridge Construction: $45K revenue, $24K expenses, $21K profit
    • Bondi E-commerce: $24K revenue, $12K expenses, $12K profit
    • Sydney Law Firm: $25K revenue, $12K expenses, $13K profit
  6. Cole exports report to CSV for business analysis

FAQ: Common Confusions

Q1: Is a “Client” the same as a “Customer”?

Answer: It depends on context:

Best Practice: Always specify whose customer you’re referring to:

Q2: Can a User belong to multiple Tenants?

Answer: No. Each user belongs to exactly one tenant.

Rationale: Multi-tenant SaaS architecture enforces strict data isolation. If someone works for two companies (e.g., consultant), they must have two separate accounts:

Why: This simplifies Row-Level Security (RLS) and prevents accidental data leaks.

Q3: Why is the table called ClientKPI and not just Client?

Answer: Naming reflects business purpose:

Historical Context: The product focuses on performance metrics, not CRM functionality. If this were a CRM, the table would be named Client or Contact.

Q4: What happens when a Tenant deletes a Client?

Answer: Cascade delete (fail-fast approach):

  1. ClientKPI record deleted
  2. All associated Financials deleted
  3. All associated LeadEvents deleted
  4. All associated CustomMetrics deleted
  5. Deletion is irreversible (no soft deletes)

Rationale: Per Global Development Standards:

No backwards compatibility whatsoever, as that is completely out of scope and handled by git.

Best Practice: Backups are handled at the infrastructure layer (Supabase daily snapshots). No application-level rollback mechanisms.

Q5: Can a Client belong to multiple Tenants?

Answer: No. Each client belongs to exactly one tenant.

Example: If ABC Construction and Sydney Property both work on “Harbor Bridge Project”, they would each create separate ClientKPI records:

Why: Data isolation prevents cross-contamination. Each tenant tracks their own version of the project.

Q6: What’s the difference between clientId and id in the ClientKPI table?

Answer:

Use Cases:

Example:

model ClientKPI {
  id       String  @id @default(cuid())        // Internal: "ckpi_001"
  clientId String                               // External: "XERO-12345"
}

Q7: Who is the “owner” of the data in Zixly?

Answer: The Tenant owns all data within their account.

Ownership Hierarchy:

  1. Tenant owns:
    • All User records (employees they invited)
    • All ClientKPI records (clients/projects they added)
    • All time-series data (Financials, LeadEvents, CustomMetrics)
    • All Integration credentials (Xero, HubSpot API tokens)
  2. Zixly owns:
    • Platform infrastructure (servers, databases)
    • Application code
    • Aggregated anonymized analytics (for product improvement)

Data Portability: Tenants can export all their data via API or CSV. Zixly cannot access tenant data for commercial purposes without explicit consent.

Q8: What happens when a User leaves the company?

Answer: ADMIN can deactivate or delete the user:

Option 1: Soft Deactivation (Post-MVP):

Option 2: Hard Delete (MVP):

Best Practice: Deactivate rather than delete to preserve audit trail (compliance requirement).

Technical Implementation

Database Schema (Prisma)

// Root entity: Zixly's customer
model Tenant {
  id              String   @id @default(cuid())
  name            String
  industry        String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  clientKPIs      ClientKPI[]
  users           User[]
  integrations    Integration[]

  @@map("tenants")
}

// Employee of a tenant company
model User {
  id              String   @id @default(cuid())
  tenantId        String
  email           String   @unique
  role            UserRole @default(VIEWER)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  tenant          Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@index([tenantId])
  @@map("users")
}

// Tenant's customer/project
model ClientKPI {
  id              String   @id @default(cuid())
  tenantId        String
  clientId        String
  clientName      String
  industry        String?
  currency        String   @default("AUD")
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  tenant          Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  financials      Financial[]
  leadEvents      LeadEvent[]
  customMetrics   CustomMetric[]

  @@unique([tenantId, clientId])
  @@index([tenantId])
  @@map("client_kpis")
}

Row-Level Security (RLS)

Isolation Enforcement:

-- Set tenant context on login
SET app.tenant_id = 'tenant_001';

-- RLS policy ensures Sarah (ABC Construction) only sees ABC's data
CREATE POLICY tenant_isolation_users ON users
  USING (tenant_id = current_setting('app.tenant_id', true)::text);

CREATE POLICY tenant_isolation_clients ON client_kpis
  USING (tenant_id = current_setting('app.tenant_id', true)::text);

-- Even if Sarah tries to query tenant_002's data, RLS blocks it
SELECT * FROM client_kpis WHERE tenant_id = 'tenant_002';
-- Returns: 0 rows (RLS filtering applied transparently)

Testing Isolation:

// Test: User from Tenant A cannot access Tenant B's data
describe('Multi-tenant isolation', () => {
  it('prevents cross-tenant data access', async () => {
    const tenantA = await createTenant({ name: 'ABC Construction' })
    const tenantB = await createTenant({ name: 'Sydney Property' })

    const userA = await createUser({ tenantId: tenantA.id, email: 'sarah@abc.com' })
    const clientB = await createClient({ tenantId: tenantB.id, clientName: 'Bondi Project' })

    // Authenticate as userA (tenant_001)
    const session = await authenticate(userA)

    // Attempt to access tenant_002's client
    const result = await db.clientKPI.findUnique({
      where: { id: clientB.id },
    })

    expect(result).toBeNull() // RLS blocks access
  })
})

Business Context:

Technical Schema:

Operations:

Document End

Review Cycle: Quarterly or after major schema changes Next Review: 2025-12-15 Change History:

Approval: