📍 Navigation Path: AGOG Home → Constraints

AGOG System Constraints

🚫 Critical: These are HARD RULES that must NEVER be violated
⚠️ Warning: Violating these constraints will cause architectural problems
📅 Last Updated: 2025-10-31

---

Purpose of This Document

This document lists non-negotiable architectural and technical constraints for the AGOG system. These aren't suggestions or best practices - they are requirements that must be followed in all code and documentation.

If you're unsure whether a constraint applies: It probably does. Ask before violating.

If you think a constraint should change: Document the rationale in DECISION_LOG.md and discuss with Todd.

---

PostgreSQL Requirements

✅ MUST USE: PostgreSQL 15+

Rule: AGOG requires PostgreSQL version 15 or later

Rationale:

• Need uuid_generate_v7() (added in PostgreSQL 15)
• Need enhanced MERGE statement capabilities
• Need logical replication improvements
• Need performance optimizations for multi-tenant workloads

Impact:

• Deployment environments must have PostgreSQL 15+
• Cannot use earlier PostgreSQL versions
• Cannot use other databases (MySQL, SQL Server, etc.)

Examples:

-- ✅ CORRECT
-- Assumes PostgreSQL 15+ features available
CREATE TABLE example (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    data JSONB
);

-- ❌ WRONG
-- Using generic SQL or older PostgreSQL syntax
CREATE TABLE example (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- UUIDv4, not v7
    data TEXT -- Should use JSONB
);

---

✅ MUST USE: Required PostgreSQL Extensions

Rule: Install and use these PostgreSQL extensions

Required Extensions:

• uuid-ossp - For uuid_generate_v7()
• pgcrypto - For cryptographic functions (if needed)
• pg_trgm - For text search optimization (when implemented)

Installation:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

---

UUID Requirements

✅ MUST USE: UUIDv7 for Primary Keys

Rule: ALL primary keys MUST use uuid_generate_v7(), NOT gen_random_uuid()

Rationale:

• UUIDv7 is time-ordered (first 48 bits = timestamp)
• Better B-tree index performance than random UUIDs
• Easier debugging (can see record creation order)
• Still globally unique
• Industry best practice

Impact:

• All CREATE TABLE statements use uuid_generate_v7()
• All SQL examples use UUIDv7
• Migration from UUIDv4 if changing existing tables

Examples:

-- ✅ CORRECT
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL,
    customer_number VARCHAR(50) NOT NULL
);

-- ❌ WRONG - Random UUID (UUIDv4)
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- NO!
    tenant_id UUID NOT NULL,
    customer_number VARCHAR(50) NOT NULL
);

-- ❌ WRONG - SERIAL (not globally unique)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY, -- NO!
    tenant_id UUID NOT NULL,
    customer_number VARCHAR(50) NOT NULL
);

---

Multi-Tenant Requirements

✅ MUST HAVE: Tenant Isolation in All Tables

Rule: ALL tables (except system tables) MUST include tenant_id UUID NOT NULL

Rationale:

• Multi-tenant SaaS architecture foundation
• Data isolation for security and compliance
• Required for Row Level Security (RLS)
• Prevents cross-tenant data leaks

Impact:

• Every table has tenant_id column
• Every query filters by tenant_id
• Foreign keys include tenant_id for referential integrity

Examples:

-- ✅ CORRECT
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    sales_point_id UUID NOT NULL REFERENCES sales_points(id),
    order_number VARCHAR(50) NOT NULL,
    UNIQUE (tenant_id, sales_point_id, order_number)
);

-- ❌ WRONG - No tenant_id
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    order_number VARCHAR(50) NOT NULL -- Missing tenant isolation!
);

---

✅ MUST FILTER: Always Include tenant_id in Queries

Rule: ALL queries MUST filter by tenant_id (except system/tenant management queries)

Rationale:

• Prevents cross-tenant data access (security)
• Enables query optimization (partition pruning)
• Required for compliance (data isolation)
• Prevents accidental data leaks

Impact:

• WHERE clauses include tenant_id = $1
• JOIN conditions include tenant_id matching
• Indexes include tenant_id as first column

Examples:

-- ✅ CORRECT
SELECT * FROM orders
WHERE tenant_id = $1 
  AND sales_point_id = $2
  AND order_number = $3;

-- ✅ CORRECT - Index supports tenant filtering
CREATE INDEX idx_orders_lookup 
ON orders(tenant_id, sales_point_id, order_number);

-- ❌ WRONG - No tenant_id filter (security issue!)
SELECT * FROM orders
WHERE order_number = $1; -- Can access any tenant's data!

-- ❌ WRONG - Index doesn't start with tenant_id
CREATE INDEX idx_orders_lookup 
ON orders(order_number); -- Won't use partition pruning

---

✅ MUST HAVE: Sales Point Isolation for Transactional Data

Rule: Transactional tables (orders, inventory, etc.) MUST include sales_point_id

Rationale:

• Multi-location tenants need per-location isolation
• Sales points have separate inventory, pricing, workflows
• Enables location-specific reporting
• Required for distributed operations

Impact:

• Transactional tables include sales_point_id UUID NOT NULL
• Reference data (products, customers) may omit sales_point_id
• Queries for transactional data filter by both tenant_id AND sales_point_id

Examples:

-- ✅ CORRECT - Transactional data (orders)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    sales_point_id UUID NOT NULL REFERENCES sales_points(id),
    order_number VARCHAR(50) NOT NULL,
    UNIQUE (tenant_id, sales_point_id, order_number)
);

-- ✅ CORRECT - Reference data (products) - no sales_point_id
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    product_code VARCHAR(50) NOT NULL,
    UNIQUE (tenant_id, product_code)
);

-- ❌ WRONG - Order without sales_point_id
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    order_number VARCHAR(50) NOT NULL -- Missing sales_point_id!
);

---

ID Strategy Requirements

✅ MUST USE: Surrogate UUID + Business Key Pattern

Rule: Tables MUST have both a surrogate UUID id AND a unique constraint on business identifier

Rationale:

• ORMs expect single-column primary key (UUID id)
• Humans need recognizable identifiers (order_number, customer_number)
• Surrogate key for system use, business key for user-facing operations
• Unique constraint prevents duplicates within tenant scope

Pattern:

1. Surrogate Key: id UUID PRIMARY KEY DEFAULT uuid_generate_v7()
2. Business Key: UNIQUE (tenant_id, sales_point_id, {business_identifier})

Examples:

-- ✅ CORRECT - Both surrogate and business keys
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    customer_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(200) NOT NULL,
    UNIQUE (tenant_id, customer_number) -- Business key constraint
);

-- ✅ CORRECT - Transactional data includes sales_point_id
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    sales_point_id UUID NOT NULL REFERENCES sales_points(id),
    order_number VARCHAR(50) NOT NULL,
    UNIQUE (tenant_id, sales_point_id, order_number) -- Business key
);

-- ❌ WRONG - Only surrogate key, no business key
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL,
    customer_number VARCHAR(50) NOT NULL -- Should be UNIQUE constraint!
);

-- ❌ WRONG - Composite primary key (ORMs struggle with this)
CREATE TABLE customers (
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    customer_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(200) NOT NULL,
    PRIMARY KEY (tenant_id, customer_number) -- NO! Use surrogate id
);

---

Material Tracking Requirements

✅ MUST IMPLEMENT: End-to-End Lot Genealogy

Rule: Material tracking with lot genealogy is ARCHITECTURAL FOUNDATION, not optional feature

Rationale:

• Core competitive differentiator
• Required for print industry compliance
• Enables quality root-cause analysis
• Customer requirement (raw materials → finished goods traceability)

Impact:

• All material-related tables include lot tracking
• Parent-child relationships tracked (lot genealogy)
• Material movements logged
• Cannot skip lot tracking to "simplify" implementation

Examples:

-- ✅ CORRECT - Lot tracking in material tables
CREATE TABLE inventory_lots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL,
    sales_point_id UUID NOT NULL,
    lot_number VARCHAR(100) NOT NULL,
    material_id UUID NOT NULL,
    parent_lot_id UUID, -- For genealogy
    UNIQUE (tenant_id, sales_point_id, lot_number)
);

-- ✅ CORRECT - Track material consumption
CREATE TABLE material_consumption (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL,
    work_order_id UUID NOT NULL,
    lot_id UUID NOT NULL REFERENCES inventory_lots(id),
    quantity_consumed DECIMAL(18,4) NOT NULL
);

-- ❌ WRONG - Generic inventory without lot tracking
CREATE TABLE inventory (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    tenant_id UUID NOT NULL,
    material_id UUID NOT NULL,
    quantity DECIMAL(18,4) NOT NULL -- No lot tracking!
);

---

Schema-Driven Development Requirements

✅ MUST USE: YAML Schemas as Source of Truth

Rule: Entity definitions MUST start as YAML schemas before code generation

Rationale:

• Single source of truth (DRY principle)
• Enables AI code generation
• Structured input → higher quality output
• Self-documenting

Impact:

• Define entities in Implementation/print-industry-erp/data-models/schemas/*.yaml
• Generate TypeScript types, SQL DDL, API endpoints from schemas
• Update schema first, then regenerate code
• Do not hand-write what can be generated

Examples:

# ✅ CORRECT - YAML schema first
# File: schemas/customer.yaml
entity: Customer
description: Customer master data
fields:
  - name: id
    type: uuid
    primaryKey: true
    default: uuid_generate_v7()
  
  - name: tenant_id
    type: uuid
    nullable: false
    references: tenants.id
  
  - name: customer_number
    type: varchar(50)
    nullable: false
    businessKey: true

Process:

1. Define entity in YAML
2. AI generates TypeScript types, SQL DDL, API routes
3. Human reviews and refines generated code
4. Commit both YAML and generated code

---

Documentation Requirements

✅ MUST HAVE: Navigation Path in All Documentation

Rule: ALL documentation files MUST include Navigation Path at top and bottom

Rationale:

• Prevents users getting lost in nested documentation
• Provides context (where am I?)
• Enables quick navigation
• International team-friendly (clear, literal terminology)

Pattern:

**📍 Navigation Path:** [AGOG Home](../README.md) → Parent → Current

# Document Title

[content]

---

[⬆ Back to top](#anchor) | [🏠 AGOG Home](../README.md) | [Parent](../README.md)

Impact:

• All new documentation includes Navigation Path
• Update existing documentation to add Navigation Path
• Remove old [← Back] links (redundant with bottom navigation)

Standard: See .github/NAVIGATION_PATH_STANDARD.md

---

✅ MUST AVOID: Unvalidated Performance Claims

Rule: Do NOT include specific percentages or performance claims without source attribution

Rationale:

• Credibility - unvalidated claims hurt trust
• Accuracy - targets vs. proven results must be clear
• Professional - marketing speak doesn't belong in technical docs

Impact:

• Remove unvalidated percentages from technical docs
• Qualify claims in business docs ("target outcome", "based on industry research")
• Use qualitative language where specific numbers not essential

Examples:

<!-- ✅ CORRECT - Qualified claim -->
Material tracking enables faster root cause analysis compared to 
systems without end-to-end traceability.

<!-- ✅ CORRECT - Qualified target -->
**Target Outcome:** Based on industry research, lot genealogy can 
reduce time to identify defect root cause by 70-90%.

<!-- ❌ WRONG - Unvalidated claim -->
AGOG provides 90% faster root cause analysis.

---

Deployment Requirements

✅ MUST USE: Blue-Green Deployment Pattern

Rule: Production deployments MUST use blue-green pattern with database replication

Rationale:

• Zero-downtime deployments
• Zero-loss rollback capability
• 48-96 hour stabilization window for confidence
• Database safety critical for production

Impact:

• Deployment requires Blue and Green environments
• Bidirectional logical replication during transition
• Cannot do direct production deployments
• Rollback plan required for every deployment

Standard: See Standards/code/blue-green-deployment.md

---

Terminology Requirements

✅ MUST USE: Standard Terminology

Rule: Use these terms consistently (not alternatives)

Rationale: International team clarity, avoid confusion

Standard Terms:

• Navigation Path (NOT "breadcrumbs")
• Multi-tenant (NOT "Multitenant" or "Multi-Tenant")
• Sales point (NOT "location" or "store")
• Lot genealogy (NOT "lot tracking" for parent-child relationships)
• Material tracking (for inventory/lot system)

Impact:

• Documentation uses standard terms
• Code comments use standard terms
• API naming uses standard terms

---

Testing Requirements (Future)

✅ MUST TEST: Multi-Tenant Isolation

Rule: ALL features MUST be tested for tenant isolation

Rationale:

• Security critical - cross-tenant access = data breach
• Compliance requirement
• Cannot ship without isolation testing

Impact:

• Write tests that verify tenant_id filtering
• Test that User A (tenant 1) cannot access User B's data (tenant 2)
• Test Row Level Security policies

---

What's NOT Constrained

These are flexible and can be adjusted based on needs:

• Specific frameworks (within TypeScript/Node.js ecosystem)
• UI library choice (React chosen but could change)
• Specific PostgreSQL minor version (15.x)
• Deployment infrastructure (as long as supports blue-green)
• Development tools and IDEs
• Code formatting preferences (as long as consistent)
• Testing frameworks (as long as comprehensive)

---

Constraint Violations

If You Violate a Constraint

Accidentally:

1. Fix immediately
2. Review related code for same issue
3. Note in commit message what was fixed

Intentionally (with good reason):

1. Document rationale in .github/DECISION_LOG.md
2. Discuss with Todd
3. Update this CONSTRAINTS.md file if constraint changes
4. Update affected standards documents

If You Think a Constraint is Wrong

1. Don't ignore it - follow it until changed
2. Document your reasoning - why should it change?
3. Propose alternative - what should it be instead?
4. Discuss with Todd - get agreement before changing
5. Update documentation - CONSTRAINTS.md, DECISION_LOG.md, affected standards

---

Enforcement

Human Review:

• Todd reviews all code and documentation
• Violations caught in review process

Automated (Future):

• Linting rules for code
• Schema validation for YAML
• SQL linting for database scripts
• Documentation structure validation

---

Quick Reference Checklist

Before creating a table:

• Uses uuid_generate_v7() for primary key?
• Includes tenant_id UUID NOT NULL?
• Includes sales_point_id if transactional?
• Has UNIQUE constraint on business key?
• Pattern: UNIQUE (tenant_id, [sales_point_id,] business_identifier)?

Before writing a query:

• Filters by tenant_id?
• Filters by sales_point_id if transactional?
• Index includes tenant_id as first column?

Before creating documentation:

• Includes Navigation Path at top?
• Includes bottom navigation with horizontal rule?
• No unvalidated percentage claims?
• Uses standard terminology?

Before making architectural decision:

• Violates any constraints in this document?
• Documented in .github/DECISION_LOG.md?
• Discussed with Todd?

---

Related Documentation

• .github/DECISION_LOG.md - WHY constraints exist
• Standards/data/database-standards.md - Implementation details
• .github/AI_ONBOARDING.md - Quick patterns reference
• Standards/code/schema-driven-development.md - Schema workflow

---

⚠️ When in doubt, follow these constraints. They exist for good reasons documented in DECISION_LOG.md.

---

⬆ Back to top | 🏠 AGOG Home | 📋 Decision Log