Skip to content

SAAR Biotech — Client, Agreement & Order Schema

Technical & User Guide

Schema: saar_biotech Last updated: August 2026 Purpose: Complete reference for the redesigned client/agreement/brand/order structure — why it exists, how it works, and how to debug or extend it.

Table of Contents

  1. The Original Problem
  2. Core Design Principle
  3. Entity Overview (What Each Table Means)
  4. Full Table Reference
  5. 4.1 tbl_client
  6. 4.2 tbl_client_address
  7. 4.3 tbl_mfg_agreements
  8. 4.4 tbl_PRE_SALES_ORDER_APPROVAL_ITEMS
  9. 4.5 tbl_order_Details
  10. 4.6 tbl_client_relationship
  11. 4.7 tbl_marketing_team_member
  12. The Two Core Use Cases, Solved
  13. Enums / CHECK Constraints — Full Glossary
  14. Triggers & Functions — Full Reference
  15. Business Logic: How brand_ownership Is Derived
  16. Reassignment Cascade Logic (Explained)
  17. Common Queries (Cheat Sheet)
  18. Naming Conventions Used In This Schema
  19. Known Open Items / Decisions Still Pending
  20. Migration Notes
  21. Glossary (Plain-English)

1. The Original Problem

The original tbl_client table, joined to orders via a single CLIENT_ID_I foreign key, was being asked to answer three independent questions at once:

  1. Who do we bill?
  2. Whose agreement licenses this product/brand?
  3. What's the correct marketing name to print?

One column cannot hold three independent answers. When those answers diverged — a sub-division ordering under a separate agreement, or an intermediate client billing on behalf of an unrelated end-client — the old schema had nowhere honest to put the divergence. This showed up as:

  • Slash-separated client names typed into CLIENT_NAME_C (e.g. "Benedis Life Sciences / Biocyte Organics Pvt. Ltd.")
  • MARG_NAME_SAAR left blank on many rows, with no enforcement against a real agreement
  • BRAND_OWNERSHIP and CLIENT_TYPE_C — two manually-typed flags that could silently contradict each other
  • PARENT_CLIENT_ID_C populated inconsistently (or not at all), unable to represent two independent relationships to the same client at once

2. Core Design Principle

Don't store a fact as a flag if it can be derived from structure. Don't force two independent relationships into one column.

Every table and trigger in this redesign follows one of two patterns:

  • Structural, permanent facts (e.g. "this client is a sub-division of that client") Ô→ stored explicitly, in a dedicated table, because they don't change per transaction.
  • Transactional, per-order facts (e.g. "who billed this order, and whose agreement governs it") Ô→ resolved through foreign keys and derived via query/trigger, never manually typed, because they can differ every single order.

Two real-world business cases drove this entire redesign:

  • Sub-division case: A client has an internal division or separately-registered sub-company, each potentially with its own agreement and marketing name.
  • Intermediate / third-party case: Client A places an order on behalf of Client B. Client A is billed; Client B's agreement governs the product's marketing name and rights.

Both cases turned out to need the same underlying mechanism — see Section 5.

3. Entity Overview

Table Answers the question...
tbl_client Who is this legal/billing entity?
tbl_client_address Where are they located?
tbl_mfg_agreements What contract governs a client's marketing rights? Bridges Billing Client and Marketing Client.
tbl_order_Details Who is billed (CLIENT_ID_I), and who is the marketing client (MKT_CLIENT_ID_I)?
tbl_PRE_SALES_ORDER_APPROVAL_ITEMS What specific compliance documents (DL, FL, Agreement) are missing for this order to proceed?
tbl_client_relationship Which clients are structurally or operationally linked to which?
tbl_marketing_team_member Which marketing person belongs to which team, and who heads it?

4. Full Table Reference

4.1 tbl_client

The legal/billing entity table. One row per real party you can invoice or hold an agreement with.

Key columns: - CLIENT_ID_I — primary key (character/VARCHAR type, confirmed — not integer, despite the _I suffix) - CLIENT_NAME_C — legal name - CLIENT_TYPE_C — business classification enum (see Section 6) - MARKETING_PERSON_C — the salesperson/marketing staff currently assigned to this client - GST_NO_C, DRUG_LICENSE_NO_C — statutory identifiers

Removed: PARENT_CLIENT_ID_C — migrated into tbl_client_relationship and dropped, since it could only express one relationship at a time and duplicated what the relationship table now owns. See Migration Notes.

What NOT to store here anymore: marketing/brand names, agreement details, brand ownership — these all moved to tbl_agreement / tbl_brand / tbl_order_details.

4.2 tbl_client_address

Unchanged in structure — still one-to-many, joined via CLIENT_ID_I. A dedicated tbl_client_address_map junction table (to deduplicate addresses shared across multiple clients) was designed but deliberately deferred — see Section 12.

Computed column: C_ADDRESS_DISPLAY — auto-generated multi-line display string, built from address lines, city, pincode, state, and country. Handles blank/NULL fields gracefully (skips empty groups instead of leaving stray blank lines). Newlines are real CHR(10) characters in storage — display behavior depends on the client reading it (see Section 7, fn_address_display).

4.3 tbl_mfg_agreements

New table. Acts as the master record for unique agreement contracts. It bridges the three core entities: Billing Client, Marketing Client, and Manufacturing Organization.

Column Purpose
ID PK (UUID)
FILE_NO_C Physical file reference number
SERIAL_NO_C Unique serial number for the agreement
BILLING_CLIENT_ID_I FK → tbl_client — who is billed
MARKETING_CLIENT_ID_C FK → tbl_client — whose brand/marketing name is used
ORGANIZATION_C The manufacturing organization (SAAR Biotech)
ISSUE_DATE_D, EXPIRY_DATE_D Validity window

By extracting agreements into this table, we avoid duplicate uploads. When a new order is placed, the system checks this table for an active agreement matching the client. This table represents the single source of truth for compliance.

4.4 tbl_PRE_SALES_ORDER_APPROVAL_ITEMS

New table. Tracks the specific missing documents, licenses, or requirements that are currently holding up an order's approval.

Column Purpose
ID PK (UUID)
ORDER_NO_C FK → tbl_order_Details — the order on hold
ITEM_NAME_C E.g. DL, FL, GST, MFG Agreement
RESOLVED_TS When this specific item was provided/resolved

Instead of a single "Hold Reason" dropdown, this table allows multiple items to be pending simultaneously for a single order, creating a full audit trail of compliance checks.

4.5 tbl_order_Details

Modified. The single CLIENT_ID_I FK is split into two independent references:

Column Purpose
CLIENT_ID_I FK → tbl_client (Billing) — who is actually invoiced
MKT_CLIENT_ID_I FK → tbl_client (Marketing) — whose agreement/brand governs the product

This two-FK split is the mechanism that solves both the sub-division and intermediate use cases with zero special-casing — see Section 5.

4.6 tbl_client_relationship

New table. Records structural/operational links between two clients. This is not for per-order facts — it's for relationships that persist across many orders.

CREATE TABLE saar_biotech.tbl_client_relationship (
  "RELATIONSHIP_ID"       SERIAL PRIMARY KEY,
  "PARENT_CLIENT_ID_C"    VARCHAR(255) NOT NULL REFERENCES saar_biotech.tbl_client("CLIENT_ID_I"),
  "CHILD_CLIENT_ID_C"     VARCHAR(255) NOT NULL REFERENCES saar_biotech.tbl_client("CLIENT_ID_I"),
  "RELATIONSHIP_TYPE_C"   TEXT NOT NULL,
  "CREATED_TS"            TIMESTAMP NOT NULL DEFAULT now(),
  "UPDATED_TS"            TIMESTAMP NOT NULL DEFAULT now(),
  "CREATED_BY"            VARCHAR(100),
  "UPDATED_BY"            VARCHAR(100),

  CONSTRAINT chk_relationship_type CHECK ("RELATIONSHIP_TYPE_C" = ANY (ARRAY['Sub Division'::text, 'Marketing Client'::text])),
  CONSTRAINT chk_not_self_link CHECK ("PARENT_CLIENT_ID_C" <> "CHILD_CLIENT_ID_C"),
  CONSTRAINT uq_relationship UNIQUE ("CHILD_CLIENT_ID_C", "RELATIONSHIP_TYPE_C")
);

CREATE INDEX idx_relationship_parent ON saar_biotech.tbl_client_relationship ("PARENT_CLIENT_ID_C");
CREATE INDEX idx_relationship_child  ON saar_biotech.tbl_client_relationship ("CHILD_CLIENT_ID_C");

Direction convention (important — easy to get backwards):

  • PARENT_CLIENT_ID_C = the billing party — the one who invoices / routes the order.
  • CHILD_CLIENT_ID_C = the marketing party — the one whose agreement/brand governs the order.

Sitting on a client's own record: - "Whom am I billing for?" Ô→ I am the parent Ô→ look up rows where PARENT_CLIENT_ID_C = me Ô→ the CHILD_CLIENT_ID_C values are who I'm billing/marketing for. - "Who bills on my behalf?" Ô→ I am the child Ô→ look up rows where CHILD_CLIENT_ID_C = me Ô→ the PARENT_CLIENT_ID_C values are my billing parent(s).

RELATIONSHIP_TYPE_C — live values: 'Sub Division', 'Marketing Client' (exact case and spacing as stored).

**uq_relationship UNIQUE(CHILD_CLIENT_ID_C, RELATIONSHIP_TYPE_C)** — a child can have only **one active parent per relationship type**. This means a client can have at most oneSub Divisionparent AND, independently, at most one currentMarketing Client` parent — but not two of the same type at once.

4.7 tbl_marketing_team_member

New table. Flat team structure — teams of ~3 marketing people, each with one designated head. No multi-level hierarchy.

CREATE TABLE saar_biotech.tbl_marketing_team_member (
  "TEAM_MEMBER_ID"      SERIAL PRIMARY KEY,
  "TEAM_NAME_C"          VARCHAR(100) NOT NULL,
  "USER_ID"              VARCHAR(255) NOT NULL REFERENCES saar_biotech.tbl_user("USER_ID"),
  "IS_TEAM_HEAD_B"       BOOLEAN NOT NULL DEFAULT FALSE,
  "CREATED_TS"           TIMESTAMP NOT NULL DEFAULT now(),
  "UPDATED_TS"           TIMESTAMP NOT NULL DEFAULT now(),
  "CREATED_BY"           VARCHAR(100),
  "UPDATED_BY"           VARCHAR(100),

  CONSTRAINT uq_user_team UNIQUE ("USER_ID")
);

CREATE INDEX idx_marketing_team_member_team ON saar_biotech.tbl_marketing_team_member ("TEAM_NAME_C");

CREATE UNIQUE INDEX uq_one_head_per_team
ON saar_biotech.tbl_marketing_team_member ("TEAM_NAME_C")
WHERE "IS_TEAM_HEAD_B" = TRUE;
  • UNIQUE("USER_ID") — each person belongs to exactly one team at a time.
  • Partial unique index guarantees at most one IS_TEAM_HEAD_B = TRUE row per team — the database itself blocks two heads on the same team.

5. The Two Core Use Cases, Solved

Case A — Sub-division

A client has an internal division or separately-registered sub-company with its own marketing name and possibly its own agreement.

Resolution: - tbl_client_relationship row: RELATIONSHIP_TYPE_C = 'Sub Division', parent = main client, child = sub-division client. - A tbl_mfg_agreements row bridges the parent and sub-division. - On an order, CLIENT_ID_I (Billing) may be either the parent or the sub-division itself, and MKT_CLIENT_ID_I resolves to whichever agreement actually applies — no special "is this a sub-division order" flag needed.

Case B — Intermediate / third-party billing

Client A places an order on behalf of Client B. Client A is billed. Client B's agreement governs the brand.

Resolution: - tbl_client_relationship row: RELATIONSHIP_TYPE_C = 'Marketing Client', parent = intermediate (billing) client, child = end-client (agreement holder). - On the order: CLIENT_ID_I = Client A, MKT_CLIENT_ID_I = Client B.

The key insight: both cases are solved by the same two-FK order structure (CLIENT_ID_I + MKT_CLIENT_ID_I) and the same relationship table, differentiated only by RELATIONSHIP_TYPE_C.

6. Enums / CHECK Constraints — Full Glossary

tbl_client.CLIENT_TYPE_C (true Postgres ENUM: saar_biotech.tbl_client_client_type_c)

Value Meaning
GOVT Government/public-sector client
HOME_PARTY Internal / in-house — our own firm's entity
THIRD_PARTY Independent client who bills us directly (this is the original meaning — predates this redesign)
POTENTIAL Prospective client, not yet onboarded
ASSOCIATE_CLIENT New value. A linked client not yet billing directly.

⚠️ Naming collision warning: CLIENT_TYPE_C.THIRD_PARTY refers to an independent client who bills us directly. It should not be confused with third-party billing arrangements derived from matching CLIENT_ID_I and MKT_CLIENT_ID_I.

Determining Ownership (Derived logic)

Brand ownership is never manually typed. It's computed by comparing the order's billing client against the marketing client, and checking tbl_client_relationship for a link:

  • OWN_BRAND: CLIENT_ID_I = MKT_CLIENT_ID_I
  • SUB_DIV_BRAND: Clients differ, but are linked via Sub Division in tbl_client_relationship
  • THIRD_PARTY: Clients differ, with no relationship link — a true unrelated intermediate

tbl_client_relationship.RELATIONSHIP_TYPE_C (TEXT + CHECK)

Value (exact string) Meaning
Sub Division Structural/permanent — child is organizationally part of parent
Marketing Client The child is a marketing/brand-owning client that the parent currently bills/routes for

7. Triggers & Functions — Full Reference

Function Fires on Purpose
fn_calculations_handler() (legacy — being phased out) Originally handled both tbl_leads profit calculations AND tbl_client_address display in one multi-table function. Split into the two functions below for safety/maintainability. Keep briefly as a safety net, then drop once both new triggers are confirmed working.
fn_leads_calculations() BEFORE INSERT OR UPDATE on tbl_leads Computes C_PROFIT_I, C_TOTAL_SALE_I, C_TOTAL_PROFIT_I, C_PROFIT_PERCENT
fn_address_display() BEFORE INSERT OR UPDATE on tbl_client_address Builds C_ADDRESS_DISPLAY from address lines + city/pincode + state/country, skipping empty groups cleanly
fn_timestamp_update() BEFORE UPDATE on multiple tables (branches on TG_TABLE_NAME) Sets UPDATED_TS (or table-specific equivalents like UPDATED_AT_D) to CURRENT_TIMESTAMP(3). Reused for tbl_client_relationship and tbl_marketing_team_member rather than writing new functions — this is the established schema-wide convention.
fn_prevent_relationship_cycle() BEFORE INSERT OR UPDATE on tbl_client_relationship Blocks a reverse-direction duplicate — prevents A being parent of B while B is also parent of A
fn_prevent_subdivision_marketing_conflict() BEFORE INSERT OR UPDATE on tbl_client_relationship Blocks a client from being both a Sub Division child of one company AND a Marketing Client parent (biller) for others at the same time — business rule, explicitly chosen to block, not allow
fn_cascade_client_reassignment() AFTER UPDATE on tbl_client When MARKETING_PERSON_C changes on a client, cascades the new assignee to all linked children (both Sub Division and Marketing Client) — but only to children whose current assignee matched the parent's previous assignee. Children already deliberately assigned to someone else are left untouched.
fn_lead_update_audit() BEFORE UPDATE on tbl_leads (pre-existing, unmodified) Handles optimistic locking (VERSION_NUM) for AppSheet sync conflicts, auto-increments version for backend/SQL updates, and logs full before/after row state to LEAD_AUDIT_LOG on conflict. Reads but does not set UPDATED_BY — that's set by the application layer.

Full SQL — new/modified functions and triggers

-- Split from fn_calculations_handler: leads-only logic
CREATE OR REPLACE FUNCTION saar_biotech.fn_leads_calculations()
RETURNS trigger LANGUAGE plpgsql AS $function$
BEGIN
  NEW."C_PROFIT_I" := NEW."RATE_I" - NEW."COSTING_I";
  NEW."C_TOTAL_SALE_I" := NEW."RATE_I" * NEW."QUANTITY_I";
  NEW."C_TOTAL_PROFIT_I" := (NEW."RATE_I" - NEW."COSTING_I") * NEW."QUANTITY_I";
  IF NEW."RATE_I" <> 0 THEN
    NEW."C_PROFIT_PERCENT" := ((NEW."RATE_I" - NEW."COSTING_I") / NEW."RATE_I") * 100;
  ELSE
    NEW."C_PROFIT_PERCENT" := NULL;
  END IF;
  RETURN NEW;
END;
$function$;

-- Split from fn_calculations_handler: address display logic
CREATE OR REPLACE FUNCTION saar_biotech.fn_address_display()
RETURNS trigger LANGUAGE plpgsql AS $function$
BEGIN
  NEW."C_ADDRESS_DISPLAY" := CONCAT_WS(
    CHR(10),
    NULLIF(CONCAT_WS(CHR(10),
      NULLIF(NEW."ADDRESS_LINE_1", ''),
      NULLIF(NEW."ADDRESS_LINE_2", ''),
      NULLIF(NEW."ADDRESS_LINE_3", ''),
      NULLIF(NEW."ADDRESS_LINE_4", '')
    ), ''),
    NULLIF(CONCAT_WS(' - ',
      NULLIF(NEW."CITY_C", ''),
      NULLIF(NEW."PIN_CODE_C", '')
    ), ''),
    NULLIF(CONCAT_WS(', ',
      NULLIF(NEW."STATE_C", ''),
      NULLIF(NEW."COUNTRY_C", '')
    ), '')
  );
  RETURN NEW;
END;
$function$;

DROP TRIGGER IF EXISTS trg_client_address_display ON saar_biotech.tbl_client_address;
CREATE TRIGGER trg_client_address_display
BEFORE INSERT OR UPDATE ON saar_biotech.tbl_client_address
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_address_display();

-- Cycle prevention
CREATE OR REPLACE FUNCTION saar_biotech.fn_prevent_relationship_cycle()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM saar_biotech.tbl_client_relationship
    WHERE "PARENT_CLIENT_ID_C" = NEW."CHILD_CLIENT_ID_C"
      AND "CHILD_CLIENT_ID_C" = NEW."PARENT_CLIENT_ID_C"
  ) THEN
    RAISE EXCEPTION 'Cannot create relationship: % is already the parent of % (reverse link exists)',
      NEW."CHILD_CLIENT_ID_C", NEW."PARENT_CLIENT_ID_C";
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_relationship_cycle
BEFORE INSERT OR UPDATE ON saar_biotech.tbl_client_relationship
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_prevent_relationship_cycle();

-- Sub Division / Marketing Client conflict block
CREATE OR REPLACE FUNCTION saar_biotech.fn_prevent_subdivision_marketing_conflict()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW."RELATIONSHIP_TYPE_C" = 'Sub Division' THEN
    IF EXISTS (
      SELECT 1 FROM saar_biotech.tbl_client_relationship
      WHERE "PARENT_CLIENT_ID_C" = NEW."CHILD_CLIENT_ID_C"
        AND "RELATIONSHIP_TYPE_C" = 'Marketing Client'
    ) THEN
      RAISE EXCEPTION 'Cannot link %: already acting as a billing parent for a Marketing Client relationship',
        NEW."CHILD_CLIENT_ID_C";
    END IF;
  END IF;

  IF NEW."RELATIONSHIP_TYPE_C" = 'Marketing Client' THEN
    IF EXISTS (
      SELECT 1 FROM saar_biotech.tbl_client_relationship
      WHERE "CHILD_CLIENT_ID_C" = NEW."PARENT_CLIENT_ID_C"
        AND "RELATIONSHIP_TYPE_C" = 'Sub Division'
    ) THEN
      RAISE EXCEPTION 'Cannot link %: already a Sub Division child of another client',
        NEW."PARENT_CLIENT_ID_C";
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_subdivision_marketing_conflict
BEFORE INSERT OR UPDATE ON saar_biotech.tbl_client_relationship
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_prevent_subdivision_marketing_conflict();

-- Reassignment cascade (final version — ALL relation types, only in-sync children)
CREATE OR REPLACE FUNCTION saar_biotech.fn_cascade_client_reassignment()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW."MARKETING_PERSON_C" IS DISTINCT FROM OLD."MARKETING_PERSON_C" THEN
    UPDATE saar_biotech.tbl_client c
    SET "MARKETING_PERSON_C" = NEW."MARKETING_PERSON_C"
    WHERE c."CLIENT_ID_I" IN (
      SELECT "CHILD_CLIENT_ID_C"
      FROM saar_biotech.tbl_client_relationship
      WHERE "PARENT_CLIENT_ID_C" = NEW."CLIENT_ID_I"
    )
    AND c."MARKETING_PERSON_C" IS NOT DISTINCT FROM OLD."MARKETING_PERSON_C";
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_cascade_client_reassignment
AFTER UPDATE ON saar_biotech.tbl_client
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_cascade_client_reassignment();

-- Timestamp triggers reusing the existing shared function
CREATE TRIGGER trg_client_relationship_ts
BEFORE UPDATE ON saar_biotech.tbl_client_relationship
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_timestamp_update();

CREATE TRIGGER trg_marketing_team_member_ts
BEFORE UPDATE ON saar_biotech.tbl_marketing_team_member
FOR EACH ROW
EXECUTE FUNCTION saar_biotech.fn_timestamp_update();

8. Business Logic: How Brand Ownership Is Derived

"Brand ownership" on tbl_order_Details is a computed concept based on comparing the order's billing client (CLIENT_ID_I) against the marketing client (MKT_CLIENT_ID_I), and checking tbl_client_relationship for a link:

CASE
  WHEN o."CLIENT_ID_I" = o."MKT_CLIENT_ID_I"
    THEN 'OWN_BRAND'
  WHEN EXISTS (
    SELECT 1 FROM saar_biotech.tbl_client_relationship r
    WHERE r."RELATIONSHIP_TYPE_C" = 'Sub Division'
      AND ((r."PARENT_CLIENT_ID_C" = o."CLIENT_ID_I" AND r."CHILD_CLIENT_ID_C" = o."MKT_CLIENT_ID_I")
        OR (r."PARENT_CLIENT_ID_C" = o."MKT_CLIENT_ID_I" AND r."CHILD_CLIENT_ID_C" = o."CLIENT_ID_I"))
  ) THEN 'SUB_DIV_BRAND'
  ELSE 'THIRD_PARTY'
END AS brand_ownership

This can be implemented as a view or computed at query time — the schema doesn't mandate one specific mechanism, but it should never be a value a user types into a form directly, since that reintroduces the exact drift problem this redesign eliminated.

9. Reassignment Cascade Logic (Explained)

Rule (final, as confirmed): When a client's MARKETING_PERSON_C is reassigned, all linked children (both Sub Division and Marketing Client relationship types) are also reassigned to the new person — but only children whose current assignee exactly matched the parent's previous assignee. A child already deliberately assigned to someone different is left untouched.

Why "only if previously in sync": this protects against silently overwriting a deliberate manual override. If someone specifically assigned a sub-division to a different salesperson than its parent, that decision persists — the cascade only catches children that were passively inheriting the parent's assignee.

Trigger: fn_cascade_client_reassignment() — see full code in Section 7.

To test after deployment: 1. Pick a client with at least one linked child (either relationship type) where the child's MARKETING_PERSON_C currently matches the parent's. 2. Update the parent's MARKETING_PERSON_C. 3. Confirm the child's MARKETING_PERSON_C updated automatically. 4. Repeat with a child that has a different assignee than the parent — confirm it does NOT change.

10. Common Queries (Cheat Sheet)

Whom is this client billing for (all linked children, both types): 

SELECT r."CHILD_CLIENT_ID_C", r."RELATIONSHIP_TYPE_C"
FROM saar_biotech.tbl_client_relationship r
WHERE r."PARENT_CLIENT_ID_C" = :client_id;

Who bills on this client's behalf (parent, if any): 

SELECT r."PARENT_CLIENT_ID_C", r."RELATIONSHIP_TYPE_C"
FROM saar_biotech.tbl_client_relationship r
WHERE r."CHILD_CLIENT_ID_C" = :client_id;

Full invoice resolution (billing + marketed-by, name and address, per order): 

SELECT
  o."ORDER_NO_C",
  bill_addr."C_ADDRESS_DISPLAY" AS billing_address,
  mkt_addr."C_ADDRESS_DISPLAY" AS marketed_by_address,
  ag."ORGANIZATION_C" AS marketed_by_org
FROM saar_biotech.tbl_order_Details o
LEFT JOIN saar_biotech.tbl_mfg_agreements ag 
  ON ag."BILLING_CLIENT_ID_I" = o."CLIENT_ID_I" 
  AND ag."MARKETING_CLIENT_ID_C" = o."MKT_CLIENT_ID_I"
JOIN saar_biotech.tbl_client_address bill_addr ON bill_addr."CLIENT_ID_I" = o."CLIENT_ID_I"
JOIN saar_biotech.tbl_client_address mkt_addr ON mkt_addr."CLIENT_ID_I" = o."MKT_CLIENT_ID_I"
WHERE o."ORDER_NO_C" = :order_id;

Count of independently-acquired ("main") clients per salesperson: 

SELECT "MARKETING_PERSON_C", COUNT(*)
FROM saar_biotech.tbl_client
WHERE "CLIENT_TYPE_C" <> 'ASSOCIATE_CLIENT'
GROUP BY "MARKETING_PERSON_C";

Team roster with head flagged: 

SELECT "TEAM_NAME_C", "USER_ID", "IS_TEAM_HEAD_B"
FROM saar_biotech.tbl_marketing_team_member
ORDER BY "TEAM_NAME_C", "IS_TEAM_HEAD_B" DESC;

11. Naming Conventions Used In This Schema

Suffix/Pattern Meaning
_I Historically implies integer/ID — but confirmed not always literal (CLIENT_ID_I is actually character type). Always verify with information_schema.columns before assuming.
_C Character/text column
_B Boolean (assumed convention — verify against real existing columns before relying on it)
_TS Timestamp
tbl_ prefix All tables
fn_ prefix All trigger functions
trg_ prefix All triggers
chk_ / uq_ / idx_ prefix CHECK constraints / unique constraints / indexes

CHECK vs. true ENUM — deliberate choice: Newer/actively-evolving fields (brand_ownership, RELATIONSHIP_TYPE_C) use TEXT + CHECK rather than a Postgres ENUM type. This was a deliberate tradeoff: renaming or adding a CHECK value is a simple DROP/ADD CONSTRAINT with no transaction restrictions, whereas Postgres ENUM values are effectively permanent once added (no clean DROP VALUE). Given how much naming iteration happened during this design process, CHECK was chosen for flexibility. Older, stable fields (CLIENT_TYPE_C) remain true ENUM types since they were already live in production.

12. Known Open Items / Decisions Still Pending

  • tbl_client_address_map (address deduplication) — deliberately deferred. Currently, if two different legal entities share one physical address, it's stored twice with no structural link, and updates must be made in both places manually. Revisit if address drift becomes a practical problem.
  • RELATIONSHIP_TYPE_C value casing — currently 'Sub Division' / 'Marketing Client' (mixed case, spaced), inconsistent with the upper-snake-case convention used elsewhere (OWN_BRAND, GOVT, etc.). Not yet standardized — worth a cleanup pass if consistency becomes important.
  • tbl_marketing_team_member boolean suffix (_B) — assumed convention, not yet confirmed against real existing boolean columns in the schema.
  • TEAM_NAME_C repetition risk (accepted, not eliminated) — TEAM_NAME_C is free text repeated on every membership row, not backed by a canonical team-master table (see Section 4.9 for the full reasoning behind this tradeoff). A typo when adding a new member could silently create a duplicate/phantom team with no database-level error. Mitigation: any UI that sets TEAM_NAME_C should use a dropdown populated from SELECT DISTINCT "TEAM_NAME_C" FROM tbl_marketing_team_member, never a free-text field. Revisit with a proper team-master table if this ever causes a real data-quality incident.
  • Multi-level sub-division chains — the reassignment cascade trigger handles them naturally (cascades recursively via repeated AFTER UPDATE firing), but this has not been explicitly tested with 3+ levels deep.
  • fn_calculations_handler() (legacy) — should be dropped once fn_leads_calculations() and fn_address_display() are confirmed stable in production, to avoid confusion from having an unused duplicate function around.

13. Migration Notes

Migrating PARENT_CLIENT_ID_C into tbl_client_relationship: 

INSERT INTO saar_biotech.tbl_client_relationship ("PARENT_CLIENT_ID_C", "CHILD_CLIENT_ID_C", "RELATIONSHIP_TYPE_C")
SELECT "PARENT_CLIENT_ID_C", "CLIENT_ID_I", 'Sub Division'
FROM saar_biotech.tbl_client
WHERE "PARENT_CLIENT_ID_C" IS NOT NULL
ON CONFLICT DO NOTHING;
Verify row counts, check for any views/reports still referencing PARENT_CLIENT_ID_C (query pg_depend/pg_rewrite against tbl_client), then: 
ALTER TABLE saar_biotech.tbl_client DROP COLUMN "PARENT_CLIENT_ID_C";

Adding a new CLIENT_TYPE_C enum value (true Postgres ENUM — different rules from CHECK): 

ALTER TYPE saar_biotech.tbl_client_client_type_c ADD VALUE 'NEW_VALUE' AFTER 'EXISTING_VALUE';
Run as a standalone statement — cannot be combined in the same transaction as statements that immediately use the new value (pre-PG12 restriction, safest to follow regardless of version).

14. Glossary (Plain-English)

Term Plain meaning
Billing client Whoever actually gets invoiced for an order
Agreement-holder / marketing client Whoever's signed agreement governs the brand name and manufacturing rights for that order
Sub-division An internal division or separately-registered company that's structurally part of a bigger client, permanently
Marketing Client (relationship type) An end-client whose orders are currently routed/billed through another (intermediate) client — not a permanent structural fact, can change over time
Own Brand Client ordered for themselves — billing and agreement-holder are the same
Associate Client A client not yet billing us directly — either a sub-division or a currently-indirect end-client
Cascade reassignment When a client's salesperson changes, their linked children (who were following the same assignee) automatically move to the new salesperson too

End of document. For questions on any section, refer back to the relevant table/trigger definition above before making schema changes — most design decisions here were made deliberately to solve a specific real case; check Section 12 for anything still open.