Schema Compatibility

All departmental-growth-trends subtypes share a unified base schema that enables storage in a single table. The data object contains subtype-specific fields, but the overall structure is consistent across all variants.

Unified Base Schema

Every signal follows this structure regardless of subtype:

{
  "signal_id": "uuid-v4",
  "signal_type": "departmental-growth-trends",
  "signal_subtype": "multiDepartmentRapidGrowth | departmentalReallocation | normalizedGrowthSignal | departmentalGrowth[Department]",
  "detected_at": "2025-01-15T00:00:00Z",
  "association": "company",
  "company": {
    "name": "Officeworks",
    "domain": "officeworks.com.au",
    "linkedin_url": "linkedin.com/company/officeworks",
    "industries": "Retail",
    "employee_count_low": 5001,
    "employee_count_high": 10000
  },
  "data": {
    // Subtype-specific fields (see below)
  }
}

Common Fields (All Subtypes)

These fields are present in every departmental-growth-trends signal:

Field	Type	Description	Example
`signal_id`	string (UUID)	Unique signal identifier	`"a1b2c3d4-e5f6-..."`
`signal_type`	string	Always `"departmental-growth-trends"`	`"departmental-growth-trends"`
`signal_subtype`	string	Subtype identifier (see table below)	`"multiDepartmentRapidGrowth"`
`detected_at`	string (ISO 8601)	When signal was detected	`"2025-01-15T00:00:00Z"`
`association`	string	Always `"company"`	`"company"`
`company.name`	string	Company name	`"Officeworks"`
`company.domain`	string	Company domain	`"officeworks.com.au"`
`company.linkedin_url`	string	LinkedIn URL	`"linkedin.com/company/officeworks"`
`company.industries`	string	Industry classification	`"Retail"`
`company.employee_count_low`	integer	Lower bound of employee range	`5001`
`company.employee_count_high`	integer	Upper bound of employee range	`10000`
`data.summary`	string	Human-readable summary	`"Rapid growth across 5 departments..."`
`data.description`	string	Detailed description	`"Company is experiencing aggressive..."`

Subtype-Specific Data Fields

1. multiDepartmentRapidGrowth

{
  "data": {
    "summary": "Rapid growth across 5 departments...",
    "description": "Company is experiencing aggressive...",
    "departments_growing": [
      {
        "department": "Operations",
        "headcount": 907,
        "growth_1yr": 8.0,
        "growth_1yr_absolute": 67
      }
    ],
    "departments_growing_count": 5,
    "growth_threshold": 5.0,
    "fastest_growing_department": "HumanResources",
    "fastest_growth_rate": 26.0,
    "total_headcount": 5971
  }
}

Fields:

departments_growing (array) - List of growing departments
departments_growing_count (integer) - Number of departments growing
growth_threshold (number) - Minimum growth % threshold used
fastest_growing_department (string) - Department with highest growth
fastest_growth_rate (number) - Highest growth percentage
total_headcount (integer) - Total company headcount

2. departmentalReallocation

{
  "data": {
    "summary": "Significant reallocation with Finance declining...",
    "description": "Company is shifting resources...",
    "departments_growing": [
      {
        "department": "Operations",
        "headcount": 907,
        "growth_1yr": 8.0,
        "headcount_change": 67
      }
    ],
    "departments_declining": [
      {
        "department": "Finance",
        "headcount": 56,
        "growth_1yr": -2.0,
        "headcount_change": -1
      }
    ],
    "reallocation_magnitude": "moderate",
    "net_headcount_change": 150
  }
}

Fields:

departments_growing (array) - Departments experiencing growth
departments_declining (array) - Departments experiencing decline
reallocation_magnitude (string) - "minor", "moderate", or "significant"
net_headcount_change (integer) - Net change in total headcount

3. normalizedGrowthSignal

{
  "data": {
    "summary": "Growth of 15% YoY represents moderate expansion...",
    "description": "Company's 15% headcount growth is meaningful...",
    "overall_growth_1yr": 15.0,
    "employee_count": 5971,
    "growth_per_employee": 0.0025,
    "revenue_range_low": 1000000000,
    "revenue_range_high": 1000000000000,
    "growth_to_revenue_ratio": 0.000015,
    "company_size_category": "large",
    "growth_category": "moderate"
  }
}

Fields:

overall_growth_1yr (number) - Overall YoY growth percentage
employee_count (integer) - Total employee count
growth_per_employee (number) - Normalized growth metric
revenue_range_low (integer, optional) - Low end of revenue range
revenue_range_high (integer, optional) - High end of revenue range
growth_to_revenue_ratio (number, optional) - Growth relative to revenue
company_size_category (string) - "small", "medium", "large", or "enterprise"
growth_category (string) - "slow", "moderate", "rapid", or "aggressive"

4. departmentalGrowth[Department]

Example: departmentalGrowthSales

{
  "data": {
    "summary": "1,726 Sales employees account for 28.9% of total headcount (5,971).",
    "description": "The Sales team is currently 1,726 out of 5,971 employees...",
    "headcount_sales": 1726,
    "headcount_sales_growth_1yr": 0.0,
    "headcount_sales_pct_of_total": 28.9,
    "total_headcount": 5971
  }
}

Fields (pattern for all department-specific subtypes):

headcount_[department_lowercase] (integer) - Department headcount
headcount_[department_lowercase]_growth_1yr (number) - YoY growth percentage
headcount_[department_lowercase]_pct_of_total (number) - Percentage of total headcount
total_headcount (integer) - Total company headcount

Department field naming convention:

Department name converted to lowercase
Spaces replaced with underscores
"And" becomes "and"
Examples:
- Sales → headcount_sales
- Information Technology → headcount_information_technology
- Customer Success And Support → headcount_customer_success_and_support

Single Table Schema (Parquet/Relational)

For database/warehouse storage, use this structure:

Base Columns (All Signals)

Column	Type	Nullable	Description
`signal_id`	UUID	NO	Primary key
`signal_type`	VARCHAR(50)	NO	Always `"departmental-growth-trends"`
`signal_subtype`	VARCHAR(100)	NO	Subtype identifier
`detected_at`	TIMESTAMP	NO	Detection timestamp
`association`	VARCHAR(20)	NO	Always `"company"`
`company_name`	VARCHAR(255)	YES	Company name
`company_domain`	VARCHAR(255)	YES	Company domain
`company_linkedin_url`	VARCHAR(500)	YES	LinkedIn URL
`company_industries`	VARCHAR(500)	YES	Industry classification
`company_employee_count_low`	INTEGER	YES	Lower employee bound
`company_employee_count_high`	INTEGER	YES	Upper employee bound
`data`	JSONB / TEXT	NO	Subtype-specific data (JSON)

Querying by Subtype

-- Get all multi-department rapid growth signals
SELECT * FROM signals 
WHERE signal_type = 'departmental-growth-trends' 
  AND signal_subtype = 'multiDepartmentRapidGrowth';

-- Get all department-specific signals for Sales
SELECT * FROM signals 
WHERE signal_type = 'departmental-growth-trends' 
  AND signal_subtype = 'departmentalGrowthSales';

-- Get all reallocation signals
SELECT * FROM signals 
WHERE signal_type = 'departmental-growth-trends' 
  AND signal_subtype = 'departmentalReallocation';

Extracting Data Fields (PostgreSQL JSONB)

-- Extract summary from data JSON
SELECT 
  signal_id,
  company_name,
  data->>'summary' as summary,
  data->>'description' as description
FROM signals
WHERE signal_type = 'departmental-growth-trends';

-- Extract departments_growing_count for multi-department signals
SELECT 
  signal_id,
  company_name,
  (data->>'departments_growing_count')::integer as dept_count
FROM signals
WHERE signal_type = 'departmental-growth-trends'
  AND signal_subtype = 'multiDepartmentRapidGrowth';

-- Extract department-specific headcount
SELECT 
  signal_id,
  company_name,
  (data->>'headcount_sales')::integer as sales_headcount,
  (data->>'headcount_sales_growth_1yr')::numeric as sales_growth
FROM signals
WHERE signal_type = 'departmental-growth-trends'
  AND signal_subtype = 'departmentalGrowthSales';

JSONL Format (One Signal Per Line)

Each line is a complete JSON object:

{"signal_id":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","signal_type":"departmental-growth-trends","signal_subtype":"multiDepartmentRapidGrowth","detected_at":"2025-01-15T00:00:00Z","association":"company","company":{"name":"Officeworks","domain":"officeworks.com.au","linkedin_url":"linkedin.com/company/officeworks","industries":"Retail","employee_count_low":5001,"employee_count_high":10000},"data":{"summary":"Rapid growth across 5 departments...","description":"Company is experiencing aggressive...","departments_growing":[{"department":"Operations","headcount":907,"growth_1yr":8.0,"growth_1yr_absolute":67}],"departments_growing_count":5,"fastest_growing_department":"HumanResources","fastest_growth_rate":26.0,"total_headcount":5971}}
{"signal_id":"b2c3d4e5-f6a7-8901-bcde-f23456789012","signal_type":"departmental-growth-trends","signal_subtype":"departmentalReallocation","detected_at":"2025-01-15T00:00:00Z","association":"company","company":{"name":"Officeworks","domain":"officeworks.com.au","linkedin_url":"linkedin.com/company/officeworks","industries":"Retail","employee_count_low":5001,"employee_count_high":10000},"data":{"summary":"Significant reallocation...","description":"Company is shifting resources...","departments_growing":[{"department":"Operations","headcount":907,"growth_1yr":8.0,"headcount_change":67}],"departments_declining":[{"department":"Finance","headcount":56,"growth_1yr":-2.0,"headcount_change":-1}],"reallocation_magnitude":"moderate","net_headcount_change":150}}
{"signal_id":"c3d4e5f6-a7b8-9012-cdef-345678901234","signal_type":"departmental-growth-trends","signal_subtype":"departmentalGrowthSales","detected_at":"2025-01-15T00:00:00Z","association":"company","company":{"name":"Officeworks","domain":"officeworks.com.au","linkedin_url":"linkedin.com/company/officeworks","industries":"Retail","employee_count_low":5001,"employee_count_high":10000},"data":{"summary":"1,726 Sales employees account for 28.9%...","description":"The Sales team is currently 1,726...","headcount_sales":1726,"headcount_sales_growth_1yr":0.0,"headcount_sales_pct_of_total":28.9,"total_headcount":5971}}

Parquet Format Schema

signal_id: STRING
signal_type: STRING
signal_subtype: STRING
detected_at: TIMESTAMP
association: STRING
company: STRUCT<
  name: STRING,
  domain: STRING,
  linkedin_url: STRING,
  industries: STRING,
  employee_count_low: INTEGER,
  employee_count_high: INTEGER
>
data: STRING (JSON-encoded)

The data field is stored as a JSON string to accommodate varying structures across subtypes.

Field Compatibility Matrix

Field	multiDepartmentRapidGrowth	departmentalReallocation	normalizedGrowthSignal	departmentalGrowth[Dept]
`summary`	✅	✅	✅	✅
`description`	✅	✅	✅	✅
`departments_growing`	✅	✅	❌	❌
`departments_growing_count`	✅	❌	❌	❌
`departments_declining`	❌	✅	❌	❌
`reallocation_magnitude`	❌	✅	❌	❌
`overall_growth_1yr`	❌	❌	✅	❌
`company_size_category`	❌	❌	✅	❌
`growth_category`	❌	❌	✅	❌
`headcount_[dept]`	❌	❌	❌	✅
`headcount_[dept]_growth_1yr`	❌	❌	❌	✅
`headcount_[dept]_pct_of_total`	❌	❌	❌	✅
`total_headcount`	✅	✅	❌	✅

Best Practices for Single Table Storage

1. Use JSON/JSONB for Data Field

Store the data object as JSON/JSONB to accommodate varying structures:

PostgreSQL: Use JSONB type
BigQuery: Use JSON type
Parquet: Store as JSON string

2. Index on signal_subtype

Create an index on signal_subtype for efficient filtering:

CREATE INDEX idx_signal_subtype ON signals(signal_subtype);

3. Extract Common Fields (Optional)

If you frequently query specific fields, consider extracting them to columns:

ALTER TABLE signals ADD COLUMN total_headcount INTEGER;
ALTER TABLE signals ADD COLUMN overall_growth_1yr NUMERIC;

-- Populate from data JSON
UPDATE signals 
SET total_headcount = (data->>'total_headcount')::integer,
    overall_growth_1yr = (data->>'overall_growth_1yr')::numeric
WHERE signal_type = 'departmental-growth-trends';

4. Use Views for Subtype-Specific Queries

Create views for easier querying:

CREATE VIEW multi_dept_rapid_growth AS
SELECT 
  signal_id,
  company_name,
  detected_at,
  data->>'summary' as summary,
  (data->>'departments_growing_count')::integer as dept_count,
  data->>'fastest_growing_department' as fastest_dept,
  (data->>'fastest_growth_rate')::numeric as fastest_growth
FROM signals
WHERE signal_type = 'departmental-growth-trends'
  AND signal_subtype = 'multiDepartmentRapidGrowth';

Summary

✅ All subtypes are compatible with a single table structure because:

They share identical base fields (signal_id, signal_type, signal_subtype, detected_at, association, company)
The data field uses JSON/JSONB to accommodate varying structures
Common fields (summary, description) are present in all subtypes
Subtype-specific fields are contained within the data JSON object

This design enables:

Single table storage for all departmental-growth-trends signals
Efficient querying by filtering on signal_subtype
Flexible schema that accommodates future subtypes
Consistent structure across all signal types in your database