contracts_projects

Bridge table modelling the many-to-many relationship between contracts and projects. One row per (contract, project) pair: a contract that authorizes three projects produces three rows, and a project that sits under two contracts produces two rows. There is no surrogate key or unique constraint — uniqueness is enforced procedurally by the converter (delete-then-reinsert per contract).

Source Mapping

CouchDB document type: a "contract" document — bridge rows are produced by asyncconvert.py's contract_projects(obj) handler iterating the contract's projects[] array. Each element of the array becomes one row.

Column	CouchDB source	Notes
contractid	id	The contract's id.
projectid	projects[i]	Each element of the contract's projects array — a single string ID per project under this contract.

Column Reference

Column	Type	Nullable	Description
contractid	VARCHAR(255)	yes	Foreign-key reference to contracts.id. The contract this link belongs to.
projectid	VARCHAR(255)	yes	Foreign-key reference to projects.id. One project authorized by the contract.

Keys

• No primary key. This is a pure bridge table without a unique constraint. Uniqueness is enforced procedurally: on every observation of a contract, all existing rows for that contract are deleted and the current set is re-inserted (see Refresh Semantics).

Indexes

• idx_project on projectid
• idx_contract on contractid

Both ends of the bridge are indexed, so "all projects under a contract" and "all contracts a project sits under" are both fast.

Relationships

Direction	Other table	Join	Cardinality
outgoing	contracts	contracts_projects.contractid = contracts.id	many rows → one contract
outgoing	projects	contracts_projects.projectid = projects.id	many rows → one project

The bridge realizes the many-to-many relationship between contracts and projects:

• One contract can authorize many projects (the common case).
• One project can sit under many contracts (rare, but the schema permits it — (projectid, contractid) has no unique constraint).

None of these are foreign-keyed in MariaDB; joins are by string match.

Refresh Semantics

contracts_projects is rebuilt per contract document. On each observation of a contract, the converter:

1. Issues DELETE FROM contracts_projects WHERE contractid = <contract_id>.
2. Iterates the contract's projects[] array and inserts one row per element.

Removing a project from a contract upstream therefore takes effect on the next contract reindex of that contract — the stale row is removed by step 1.

A reindex with type=contract (or a full reindex) drops and rebuilds this bridge alongside contracts. There is no separate type=contracts_projects target.

Common Queries

All projects under a contract

SELECT p.id, p.name, p.startdate, p.enddate
FROM contracts_projects cp
JOIN projects p ON p.id = cp.projectid
WHERE cp.contractid = 'your_contract_id'
ORDER BY p.startdate;

All contracts a project sits under

SELECT c.id, c.name, c.licensetype
FROM contracts_projects cp
JOIN contracts c ON c.id = cp.contractid
WHERE cp.projectid = 'your_project_id'
ORDER BY c.name;

Orphan bridge rows (project missing)

A bridge row whose projectid does not match any projects.id indicates that a project was deleted upstream without the contract being re-synced. Useful as a data-quality check.

SELECT cp.contractid, cp.projectid
FROM contracts_projects cp
LEFT JOIN projects p ON p.id = cp.projectid
WHERE p.id IS NULL;

Cross-table queries that join contracts and projects together (rollups, license-type analysis, projects-under-multiple-contracts) live on the Contracts domain landing.

Pitfalls

• No primary key, no unique constraint. Duplicates are prevented procedurally (delete-then-reinsert per contract). If a partial reindex fails between the delete and the insert, a contract may briefly have no rows here. Reindex again to recover.
• Reading during an in-flight contract reindex may return zero rows. Between the DELETE and the INSERTs for a contract, WHERE contractid = <id> returns nothing. Pipelines that read this table mid-reindex should either tolerate a transient empty set or wait until the reindex completes.
• Many-to-many is permitted, not enforced unique. A project appearing under multiple contracts is rare in practice but legitimate. Do not assume (projectid, contractid) is unique across the whole table when joining — within a single contract's rows it is, but globally the schema allows the same project under several contracts.
• No FK constraints. Joins are by string match. If a project is deleted upstream without the contract being re-synced, a row may point to a missing projects.id. The "orphan bridge rows" query above surfaces this.