audits_maps

Bridge table linking an audit to the maps it covers. One row per (audit, map) pair: an audit that covers three floors of a building produces three rows here, one per floor's map.

This bridge lives in the Maps domain because it is mostly used to answer "which maps does this audit cover?" and "which audits touch this map?", but it is referenced from both the Audits domain and the Maps domain.

Source Mapping

CouchDB document type: an "audit" document, but the rows are produced by asyncconvert.py's audits_maps(obj) handler iterating the audit's maps array. Each element of obj.maps becomes one row.

Column	CouchDB source	Notes
mapid	obj.maps[i].mapID	The maps.id of one map this audit covers. The source field is camel-cased mapID; the column is lower-cased mapid (MariaDB is case-insensitive).
audit	obj.id	The audits.id this row belongs to.
projectid	obj.database	The project this audit (and therefore its maps) belongs to.
couchdbid	obj.couchDbId	Legacy CouchDB document identifier (the audit's, not the map's).

The handler only inserts when the audit document is not marked deleted (obj.deleted == 'null'). Deleted audits leave no audits_maps rows on the next refresh.

Column Reference

Column	Type	Nullable	Description
mapid	VARCHAR(255)	yes	Foreign-key reference to maps.id. The map this audit covers.
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id. The audit this link belongs to.
projectid	VARCHAR(255)	yes	The project this audit and map belong to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier of the audit document.

Keys

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

Indexes

• idx_project on projectid
• idx_audit on audit

There is no index on mapid. Queries that look up "which audits cover this map?" will scan; if you do this often on a large dataset, add an index.

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audits_maps.audit = audits.id	many rows → one audit
outgoing	maps	audits_maps.mapid = maps.id	many rows → one map
outgoing	projects	audits_maps.projectid = projects.id	many rows → one project

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

Refresh Semantics

audits_maps is rebuilt per audit document. On each observation of an audit, the converter:

1. Issues DELETE FROM audits_maps WHERE audit = <audit_id> (only when delete tracking is enabled — see add_deletes).
2. Iterates the audit's maps array and inserts one row per element.

If the audit is marked deleted (obj.deleted != 'null'), no rows are inserted — the delete in step 1 effectively removes the audit's links.

A reindex with type=audit (or a full reindex) rebuilds this bridge as part of the audit reindex. There is no separate type=audits_maps target.

Common Queries

All maps covered by a given audit

SELECT
  m.id,
  m.name,
  m.groupname
FROM audits_maps am
JOIN maps m ON m.id = am.mapid
WHERE am.audit = 'your_audit_id'
ORDER BY m.name;

All audits that cover a given map

SELECT
  a.id,
  a.name,
  a.status,
  a.creationdate
FROM audits_maps am
JOIN audits a ON a.id = am.audit
WHERE am.mapid = 'your_map_id'
  AND a.archived IS NULL
ORDER BY a.creationdate DESC;

Audits with no maps linked

SELECT a.id, a.name, a.status
FROM audits a
LEFT JOIN audits_maps am ON am.audit = a.id
WHERE am.audit IS NULL
  AND a.projectid = 'your_project_id'
  AND a.archived IS NULL;

Cross-table queries that join audits, maps, and audits_maps together live on the Maps domain landing.

Pitfalls

• No primary key, no unique constraint. Duplicates are prevented procedurally (delete-then-reinsert per audit). If a partial reindex fails between the delete and the insert, an audit may briefly have no rows here. Reindex again to recover.
• mapid is lowercase in the column, camelCase in source. The source field is mapID; the column is mapid. Normalize when joining external systems.
• A row references both an audit and a map by id. Both id values are project-scoped (see the audits and maps leaves), but because every audits_maps row carries projectid and the audit and its maps always share the same project, joining on mapid and audit within a single projectid is unambiguous.
• No mapid index. "Which audits cover this map?" queries scan the table. Fine for small projects; consider an index for large ones.