Cap Table Data Modeling: Designing and Validating a Shareholder Register System

Why Equity Data Is Harder to Get Right Than General Business Data

Public cap table summaries often look like simple percentages: founder ownership after a funding round, employee option pool size, and percentages held by financial investors, sometimes labeled as “organized from public information; not exact audited data.” When turning that into a durable system, the difficulty is much greater than it first appears.

First, equity data mixes stock and flow. Ownership percentages are snapshots at a point in time, while financing rounds, option grants and exercises, share buybacks, and debt-to-equity conversions are events that occur over time. Storing only snapshots makes history impossible to explain; storing only events makes it hard to answer “who owns how much today?” Both must be modeled together.

Second, there is more than one calculation basis. The same “8% stake” may be based on fully diluted capitalization (including unexercised options, convertible securities, and unallocated pools in the denominator) or outstanding shares. At the same point in time, the two can differ by several percentage points, and mixing them can lead directly to wrong conclusions.

Third, equity data has strong control implications. Economic rights and voting rights can be separated. In dual-class or multi-class structures, a party holding 21% of equity may control more than half of the voting rights. Calculating only economic percentages cannot answer “who controls the company?”

Fourth, equity data is highly sensitive and audit-heavy. It may involve trade secrets, personal data, and legal disclosure obligations. Therefore, source labeling, change traceability, and access control requirements are one level higher than for general business data.

The rest of the article follows a practical design order: domain model → calculation bases → validation rules → provenance → queries and visualization → engineering compliance.

Domain Model: Five Core Entities

An equity structure can be abstracted into five entities. This covers most cases, including limited liability companies, joint-stock companies, employee stock ownership platforms, and multi-tier holding groups.

1. Party
A legal person or natural person who holds or is held. Key fields: globally unique identifier, type (natural person / company / partnership / trust / employee stock ownership platform), jurisdiction, group affiliation, and concerted-action flag. Party de-duplication is one of the most error-prone parts of this type of system. The same fund may appear with short names, full names, or English names across different filings. An alias table is needed rather than direct string matching.

2. Instrument
The equity carrier type: common stock, preferred stock (potentially with multiple series), options, restricted stock units, warrants, convertible notes, or convertible bonds. Each instrument determines three things: which calculation basis it belongs to, whether it carries voting rights, and its dilution effect after conversion or exercise.

3. Holding
The quantity of a specific instrument held by a party at a specific point in time. This is cross-sectional data and must carry an effective date/time.

4. Pool
Unallocated equity reserves, such as employee incentive pools and option pools. Pools are part of the denominator and are easy to miss. A pool should distinguish “granted but unexercised” from “not yet granted”; the former is usually already associated with specific holders.

5. Equity Event
All actions that change quantities: incorporation, issuance, transfer, option grant, exercise, buyback and cancellation, debt-to-equity conversion, stock split or reverse split, and pool expansion. The system must use events as the source of truth, with snapshots derived from events.

A relational schema example:

-- Party and aliases
CREATE TABLE party (
  party_id      VARCHAR(32) PRIMARY KEY,
  party_type    VARCHAR(16) NOT NULL,     -- person / corp / fund / platform
  group_id      VARCHAR(32),              -- group by same controlling party
  is_concerted  BOOLEAN DEFAULT FALSE,    -- concerted-action flag
  region        VARCHAR(16)
);
CREATE TABLE party_alias (
  alias      VARCHAR(128),
  party_id   VARCHAR(32) REFERENCES party(party_id),
  PRIMARY KEY (alias)
);
-- Instrument definition: which basis an instrument belongs to and whether it carries voting rights
CREATE TABLE instrument (
  instrument_id  VARCHAR(32) PRIMARY KEY,
  kind           VARCHAR(24) NOT NULL,    -- common / preferred / option / rsu / note
  series         VARCHAR(16),             -- Series A/B/C
  counts_fd      BOOLEAN NOT NULL,        -- counts in fully diluted basis
  votes_per_unit NUMERIC(6,2) NOT NULL DEFAULT 1.00
);
-- Event table: append-only fact table
CREATE TABLE equity_event (
  event_id     VARCHAR(40) PRIMARY KEY,
  event_type   VARCHAR(24) NOT NULL,      -- incorporate/issue/transfer/grant/exercise/buyback/convert/split
  effective_at TIMESTAMPTZ NOT NULL,      -- effective time (may differ from recorded time)
  recorded_at  TIMESTAMPTZ NOT NULL,
  payload      JSONB NOT NULL,            -- from_party, to_party, instrument_id, units, price
  source_id    VARCHAR(40) NOT NULL
);
-- Snapshot table: materialized view
CREATE TABLE holding_snapshot (
  as_of          DATE NOT NULL,
  party_id       VARCHAR(32) NOT NULL,
  instrument_id  VARCHAR(32) NOT NULL,
  units          NUMERIC(20,4) NOT NULL,
  PRIMARY KEY (as_of, party_id, instrument_id)
);

Calculation Bases: Three Percentages and One Voting Measure

In practice, a system should produce at least three values, and each value must explicitly label its basis. Otherwise reports cannot be compared.

Basis A: Outstanding percentage
The denominator includes shares actually issued and outstanding. It excludes unexercised options and ungranted pools.

Basis B: Fully diluted percentage
The denominator includes issued shares + granted but unexercised options/restricted units + issued convertible securities (converted using agreed conversion ratios) + ungranted reserved pools. This is the default basis used in investment negotiations.

Basis C: Voting percentage
The numerator is the sum of “holding quantity × voting power per unit,” and the denominator is the sum of voting power for all parties on the same basis. In dual-class or multi-class structures, this percentage can diverge significantly from the fully diluted percentage.

Example calculation:

# basis: "outstanding" | "fully_diluted"
def holdings_ratio(rows, basis):
    num = defaultdict(float)
    den = 0.0
    for r in rows:
        if basis == "fully_diluted":
            units = r.units + r.granted_unexercised + r.convertible_shares + r.unallocated_pool
        else:
            units = r.units
        num[r.party_id] += units
        den += units
    return {p: u / den for p, u in num.items()}
def voting_ratio(rows):
    num = defaultdict(float)
    den = 0.0
    for r in rows:
        v = r.units * r.votes_per_unit   # common stock = 1; Class B stock may be 10 or 20
        num[r.party_id] += v
        den += v
    return {p: v / den for p, v in num.items()}

Control analysis should not stop at the largest shareholder. It is better to output at least three measures: voting percentage of the largest shareholder, combined voting percentage of the top five shareholders, and the Herfindahl index (the sum of squared shares). When one party controls more than half of the voting power, or is far ahead of the second-largest holder, the system should flag “concentrated control.” This is more useful for decision-making than a single percentage.

Validation Rules: Making “Total Equals 100%” an Executable Constraint

The most practical value of an equity system comes from validation. The following rules should run continuously as scheduled jobs or query views.

-- R1 The fully diluted total for each period should equal the capitalization base
SELECT as_of, SUM(units) AS total,
       ABS(SUM(units) - (SELECT base FROM cap_base WHERE base.as_of = s.as_of)) AS drift
FROM holding_snapshot s
WHERE instrument_id IN (SELECT instrument_id FROM instrument WHERE counts_fd)
GROUP BY as_of
HAVING ABS(drift) > 0.0001;
-- R2 Conservation: current total = previous total + current issuance - current buyback/cancellation + newly converted units
-- R3 No double counting in option pools: granted units must appear in holder rows, and the pool balance must be reduced accordingly
-- R4 No party may have a negative total across instruments
-- R5 Party de-duplication: one alias must not map to multiple party_id values
-- R6 Time monotonicity: as as_of increases, unvested units may only be released according to the vesting schedule

In addition to numerical validation, the system also needs semantic validation: every data point must answer “where did this number come from?” Each record should be attached to a source entity:

source: {
  id, kind:      # legal disclosure / official announcement / media summary / internal ledger / estimate
  url_or_ref,    # neutral evidence: only a verifiable document number or archive fingerprint
  retrieved_at,
  as_of_basis,   # factual time point to which the data refers
  confidence,    # high / medium / low
  audited: bool  # audited or confirmed by the shareholder
}

A critical interface convention is that any externally shown ownership chart must distinguish confirmed data from public-summary data. The two may coexist, but they must be visually distinguishable, and the system should state that official registry records and company confirmation take precedence. Outputting estimated values as facts is one of the most serious compliance risks for this type of system.

Event Sourcing and Snapshot Rebuilds

The recommended architecture is “events as source, snapshots rebuildable.” The reason is that equity history may need to be reviewed at any point in time for due diligence, dispute verification, or merger consideration calculations. The system must be able to replay history to any point.

# Rebuild snapshot for a given day
def rebuild(as_of):
    events = store.range(begin=INCORPORATE, end=as_of + 1day)   # half-open interval, ordered by effective_at
    ledger = defaultdict(float)          # (party, instrument) -> units
    pool   = {}                          # instrument -> ungranted pool balance
    for e in events:
        if e.type == "issue":     ledger[(e.to, e.inst)] += e.units
        elif e.type == "grant":   pool[e.inst] -= e.units; ledger[(e.to, e.inst)] += e.units * e.vest_ratio(as_of)
        elif e.type == "exercise": ledger[(e.to, e.inst)] += e.units
        elif e.type == "transfer": ledger[(e.from, e.inst)] -= e.units; ledger[(e.to, e.inst)] += e.units
        elif e.type == "buyback":  ledger[(e.from, e.inst)] -= e.units
        elif e.type == "split":    apply_ratio(ledger, e.ratio)   # splits and consolidations scale all balances uniformly
    return materialize(ledger, pool)

Several implementation details are easy to miss:

• Separate effective time from recorded time. Agreement signing date, closing date, and registry change date often differ. All of them should be stored, and the snapshot time should be chosen according to the business rule.
• Vesting schedules should be modeled as a separate child table (total granted, vesting cadence, acceleration terms, forfeiture/recovery). Otherwise “granted” and “actually held” will never reconcile.
• Stock splits and reverse splits are global operations. They should generate a factor event affecting the entire ledger, not be manually edited holder by holder.
• Snapshot tables should be materialized monthly or by event batch. Combined with incremental recomputation, this avoids full replay on every query.

Circular Ownership and Equity Penetration

Multi-tier structures commonly contain cross-ownership. A holding platform may hold an operating company, and the operating company may indirectly hold interests in the platform through a fund. In such cases, adding direct percentages directly will produce incorrect results. A system of linear equations must be solved.

Let A be the matrix of direct ownership percentages (A[i][j] is the percentage directly held by i in j, with diagonal entries equal to 0), and let b be the vector of directly held percentages by external investors at each level. The effective penetrated ownership satisfies:

E = (I - A)^(-1) · b        # when self-loops or circular loops exist, matrix inversion must be used instead of layer-by-layer multiplication

Two engineering cautions:

1. First detect cycles by performing reachability analysis on A. When cycles exist, layer-by-layer recursion is not allowed, otherwise it will expand indefinitely.
2. Ensure numerical stability. When the ownership matrix is sparse, iterative methods can be used, but the system must define a convergence threshold and a maximum iteration count. The result should be marked as “includes cross-ownership; solved by linear equations.”

For visualization, it is best to provide two views: an ownership tree rooted at the controlling party (node size maps voting power, and edges show direct and penetrated percentages), and a penetration pie chart centered on the invested entity (penetrated percentages are aggregated by ultimate natural persons or groups to avoid fragmentation from many intermediate layers).

Queries, Caching, and Compliance

• Read-heavy workload, layered reads and writes. Writes should go through the event table and validation. External queries should use materialized snapshots plus caching. Cache keys must include the calculation basis and as_of value; otherwise results will be mixed up.
• Permissions and data masking. Shareholder lists may contain natural-person identity information. Field-level permissions are needed. External output should generally include only party names and percentages. Identity numbers, contact details, and agreement prices should be hidden by default, and access itself should be recorded in audit logs.
• Personal information handling should follow data minimization. If only a party identifier is needed, do not store natural-person details; if aggregation is sufficient, do not expand to individuals.
• Deployment. This type of system does not require unusually complex storage or analytics capabilities. General cloud services are sufficient: object storage for archiving disclosure documents and audit snapshots, message queues for receiving event streams and peak smoothing, container platforms for stateless services, relational databases for master data, columnar analytics engines for batch penetration and historical replay calculations, and logging/monitoring services for validation-task failure alerts. If deploying on general cloud platforms such as Baidu AI Cloud, these categories can be selected accordingly; no reliance on specific proprietary product features is required.

Implementation Checklist

1. Are calculation bases explicitly named and switchable? Does every report label one of outstanding, fully diluted, or voting?
2. Are ungranted reserved pools included in the denominator? Are they de-duplicated against granted portions?
3. Are economic interests and voting rights distinguished? Are concentration metrics provided?
4. Do validation rules such as total conservation, option-pool deduction, and cross-instrument consolidation run continuously and generate alerts?
5. Does every data point have source and confidence labels? Are “public summary” and “confirmed” visually distinguishable?
6. Does circular ownership use equation solving rather than layer-by-layer multiplication?
7. Can the system fully replay any historical point in time? Are stock splits and consolidations represented by unified factor events?
8. Do natural-person fields have field-level permissions and access auditing?

The value of equity structure data is not in presenting percentages neatly. The value is that every number can be traced to an event, a source, and a point in time, and can be recomputed and verified. When calculation bases, events, and validation are done well, later financing modeling, dilution simulations, and control analysis become different views of the same foundation.