contracts

One row per contract — the commercial agreement between EdControls and a customer organization that authorizes one or more projects to run. The top-level commercial entity in the schema; rolls up to projects through the contracts_projects bridge.

Source Mapping

CouchDB document type: a "contract" document (see asyncconvert.py's contracts(obj) handler).

Column	CouchDB source	Notes
id	id	Contract identifier; globally unique within the dataset.
name	name	Human-readable contract name. Free text.
licensetype	licenseType	License tier (e.g. trial, standard, premium). Free-text from source.
priceplan	pricePlan	Pricing structure label. Free-text from source.
debtorid	computed (derived)	From debtorId in source, with a literal-empty-string fallback when the source value is 0 (i.e. unset).
source	source	Source / origin marker for the contract record.
creationdate	dates.creationDate	Parsed via parseDate(...).
lastmodifieddate	dates.lastModifiedDate	Parsed via parseDate(...).
couchdbid	couchDbId	Legacy CouchDB document identifier.

Column Reference

Column	Type	Nullable	Description
name	TEXT	yes	Human-readable contract name (free text).
licensetype	VARCHAR(255)	yes	License tier from source (e.g. trial, standard, premium).
priceplan	VARCHAR(255)	yes	Pricing structure label from source.
debtorid	VARCHAR(255)	yes	Customer / debtor identifier; empty string when the source debtorId is 0.
source	VARCHAR(255)	yes	Source / origin marker for the contract record.
creationdate	DATETIME	yes	When the contract was created upstream.
lastmodifieddate	DATETIME	yes	When the upstream contract was last edited.
id	VARCHAR(255)	no	Primary key. Contract identifier.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier.

Keys

• Primary key: id

Indexes

• idx_contract on id

The index on id duplicates the primary-key index. It exists in sql.py and is harmless; treat it as redundant.

Relationships

Direction	Other table	Join	Cardinality
incoming	contracts_projects	contracts_projects.contractid = contracts.id	one contract → many bridge rows
transitive	projects (via bridge)	contracts.id = contracts_projects.contractid AND contracts_projects.projectid = projects.id	many-to-many

There is no direct foreign key from projects back to contracts; all contract-to-project navigation goes through the contracts_projects bridge. None of these are foreign-keyed in MariaDB; joins are by string match.

Refresh Semantics

A row is upserted when its contract document is observed in source data: the inserter does DELETE FROM contracts WHERE id = <id> followed by an INSERT. There is no archive column — once a contract stops being emitted upstream, the row remains in the table until the next full or contract reindex drops the table.

A reindex with type=contract (or a full reindex) drops the contracts table (and contracts_projects) and rebuilds both from source. See backend/services/sql.py for the drop and setup paths.

Common Queries

List all contracts

SELECT id, name, licensetype, priceplan, creationdate
FROM contracts
ORDER BY creationdate DESC;

Contracts created in a window

SELECT id, name, licensetype, creationdate
FROM contracts
WHERE creationdate >= '2026-01-01'
  AND creationdate <  '2027-01-01'
ORDER BY creationdate;

Recently modified contracts

SELECT id, name, lastmodifieddate
FROM contracts
WHERE lastmodifieddate IS NOT NULL
ORDER BY lastmodifieddate DESC
LIMIT 50;

Cross-table queries (contracts and their projects, projects under multiple contracts, contract portfolio by license type) live on the Contracts domain landing.

Pitfalls

• No archive column. Unlike projects, audits, tickets, and maps, there is no archived field on contracts. A contract that is no longer active in source remains here until a reindex drops the table. Filter by lastmodifieddate if you need a sense of recency.
• debtorid is empty-string when source is 0. The inserter treats a source debtorId of 0 as "not set" and writes the empty string. Filter on debtorid <> '' rather than debtorid IS NOT NULL when you want contracts with a real billing link.
• licensetype and priceplan are free text. They reflect whatever string the upstream system emitted. There is no closed enum; group by these for rollups, but expect occasional drift in spelling and casing.
• No FK constraints. Joins through contracts_projects are by string match; if a project is deleted upstream without the contract being re-synced, a contracts_projects row may point to a missing projects.id.
• id index duplicates the primary-key index. idx_contract on id is redundant given id is the primary key. Harmless but noted for completeness.