Organisations

The organisations domain tracks the companies and institutions behind every drug in the knowledge graph — pharmaceutical companies, biotechs, CROs, academic institutions, government agencies, investors, and diagnostics firms. Drug ownership is not static: it changes hands through mergers, acquisitions, licensing deals, and partnerships. This domain captures both the current state and the full history of those relationships.

Domain context

A single drug can change owners multiple times over its lifecycle. A biotech discovers a compound, licenses it to a mid-cap for Phase II development, and a large-cap acquires the mid-cap before approval. Multi-party deals are common — three-way licensing agreements, co-development partnerships, and spin-offs all appear in the data. The organisations domain models this reality with a flexible participant-role structure rather than rigid acquirer/target columns.

Entities

erDiagram
    organisations ||--o{ deal_participants : "participates in"
    organisations ||--o{ drug_ownerships : "owns"
    organisations |o--o| organisations : "subsidiary_of"
    deals ||--o{ deal_participants : "involves"
    deals ||--o{ deal_drugs : "includes"
    deal_drugs }o--|| drugs : "references"
    drug_ownerships }o--|| drugs : "owned drug"

organisations

Company profiles with classification and financial data. One row per entity.

Column	Purpose
bloupe_id	Stable Bioloupe identifier for the organisation
name	Canonical organisation name
synonyms	JSON array of alternative names, abbreviations, former names
organisation_type	pharma_biotech, academic, government, investor, diagnostic, cro
size_category	Market cap tier: large_cap, mid_cap, small_cap, micro_cap, nano_cap
ownership_status	public, private, subsidiary, government, non_profit
active	Whether the organisation is currently active
subsidiary_of_id	Self-referential FK to parent company
headquarters	ISO 3166-1 alpha-2 country code
stock_symbol / stock_exchange	Primary stock listing
market_cap	Market capitalization in the currency column
revenue_ttm	Trailing twelve months revenue
enterprise_value	Market cap + debt - cash
total_cash_mrq	Cash and equivalents, most recent quarter
cash_runway_months	Estimated months of runway at current burn rate (pre-revenue biotechs)
profit_margin	Net profit margin as decimal (TTM)

Indexed on organisation_type, headquarters, subsidiary_of_id, and name.

deals

M&A, licensing, co-development, distribution, and other business agreements between organisations. Tracks financial terms and geographic scope.

Column	Purpose
deal_type	merger, acquisition, asset_acquisition, license, co_development, distribution, manufacturing, collaboration, option, spin_off
announced_date	When the deal was publicly announced
closed_date	When the deal completed (NULL if pending)
status	announced, completed, terminated
total_value	Total deal value (upfront + milestones + equity)
upfront_payment	Cash upfront amount
milestone_payment	Total potential milestone payments
equity_value	Equity component value
royalties	Royalty terms description
equity_percentage	Stake percentage acquired (0-100)
currency	ISO 4217 currency code, defaults to USD
geography_include / geography_exclude	JSON arrays scoping the deal territory
therapeutic_areas	JSON array of relevant therapeutic areas

Check constraint enforces closed_date >= announced_date. Indexed on deal_type and announced_date.

deal_participants

Bridge table linking organisations to deals with their role. Each deal has at least two participants.

Column	Purpose
deal_id	FK to deals (cascade on delete)
organisation_id	FK to organisations (restrict on delete)
role	acquirer, target, licensor, licensee, collaborator, merger_partner, co_developer, distributor, initiator

Unique constraint on (deal_id, organisation_id, role) — the same organisation can hold multiple roles in a single deal (rare but valid).

deal_drugs

Links drugs to deals — which drug assets are part of a transaction.

Column	Purpose
deal_id	FK to deals (cascade on delete)
drug_id	FK to drugs (cascade on delete)

Unique constraint on (deal_id, drug_id).

drug_ownerships

Current and historical ownership records mapping drugs to owning organisations. Supports multi-party ownership with geographic scoping. Cross-references the drugs domain.

Column	Purpose
drug_id	FK to drugs (cascade on delete)
organisation_id	FK to organisations (restrict on delete)
is_current	true for active ownership, false for historical
deal_type	How ownership was obtained: originator, acquisition, license, co_development, collaboration, spin_off
geography_include / geography_exclude	JSON arrays of ISO country codes or regions
started_at	When ownership began
ended_at	When ownership ended (NULL if current)

Check constraints enforce ended_at >= started_at and that is_current = true only when ended_at IS NULL. Composite index on (drug_id, is_current) optimizes the most common query pattern: “who owns this drug right now?”

Design decisions

deal_participants bridge instead of fixed acquirer/target columns. Real-world deals frequently involve more than two parties. A three-way licensing agreement between a biotech, a regional distributor, and a co-development partner cannot fit into a two-column model. The bridge table with a role enum handles any number of participants. The unique constraint on (deal_id, organisation_id, role) prevents duplicates while allowing the same organisation to hold multiple roles.

drug_ownerships.is_current flag with check constraint. Ownership history is preserved indefinitely — rows are never deleted, only marked is_current = false with an ended_at timestamp. The check constraint (is_current = true AND ended_at IS NULL) OR is_current = false makes it impossible to have a “current” ownership with an end date. The composite index on (drug_id, is_current) means finding current owners never requires scanning historical rows.

organisation.subsidiary_of_id self-reference. Corporate hierarchy is modelled as a simple parent pointer rather than a full adjacency list or nested set. This is sufficient because the hierarchy is shallow (rarely more than 2-3 levels) and changes infrequently. Queries that need the full tree can use recursive CTEs.

restrict delete rule on organisation_id FKs. Organisations cannot be deleted if they participate in deals or own drugs. This prevents orphaned business records and forces explicit data cleanup before removal.

Materialized views

Two materialized views consume organisation data to power downstream queries.

mv_competitive_landscape — Drug-by-disease-by-phase matrix with owner info. Uses a LATERAL subquery to aggregate current_owner_names into an array, avoiding row duplication when a drug has multiple current owners. One row per (drug_id, disease_id).

mv_drug_pipeline — Pre-joined drug overview for zero-JOIN agent queries. Includes current_owner_names as an aggregated array from drug_ownerships joined to organisations. Also carries target names, technology, and highest development phase. One row per drug.

Both views use the same LATERAL + array_agg(DISTINCT ...) pattern to safely handle multi-owner drugs without breaking unique indexes.

Example queries

Find all current owners of a drug by name:

SELECT o.name, o.organisation_type, dow.deal_type, dow.geography_include
FROM drug_ownerships dow
JOIN organisations o ON o.id = dow.organisation_id
JOIN drugs d ON d.id = dow.drug_id
WHERE d.name = 'sotorasib'
  AND dow.is_current = true
ORDER BY o.name;

Top 10 deals by total value in the last year:

SELECT
  d.deal_type,
  d.total_value,
  d.currency,
  d.announced_date,
  array_agg(o.name || ' (' || dp.role || ')') AS participants
FROM deals d
JOIN deal_participants dp ON dp.deal_id = d.id
JOIN organisations o ON o.id = dp.organisation_id
WHERE d.announced_date >= NOW() - INTERVAL '1 year'
  AND d.total_value IS NOT NULL
GROUP BY d.id, d.deal_type, d.total_value, d.currency, d.announced_date
ORDER BY d.total_value DESC
LIMIT 10;

Ownership history for a drug, including which deals transferred it:

SELECT
  o.name AS owner,
  dow.deal_type,
  dow.started_at,
  dow.ended_at,
  dow.is_current,
  dow.geography_include
FROM drug_ownerships dow
JOIN organisations o ON o.id = dow.organisation_id
JOIN drugs d ON d.id = dow.drug_id
WHERE d.name = 'pembrolizumab'
ORDER BY dow.started_at NULLS FIRST;