map_groups

One row per map group — a folder that organizes related maps within a project (e.g. "Building A", "Site Plans", "Floor Plans").

Source Mapping

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

Column	CouchDB source	Notes
id	id	Group identifier; project-scoped.
name	name	Human-readable group name.
projectid	database	The project this group belongs to.
couchdbid	couchDbId	Legacy CouchDB document identifier.
creationdate	dates.creationDate	Parsed via parseDate(...).
lastmodifieddate	dates.lastModifiedDate	Parsed via parseDate(...).

Column Reference

Column	Type	Nullable	Description
id	VARCHAR(255)	no	Primary key. Group identifier; project-scoped.
name	TEXT	yes	Human-readable group name (free text).
projectid	VARCHAR(255)	yes	The project this group belongs to.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier.
creationdate	DATETIME	yes	When the group was created upstream.
lastmodifieddate	DATETIME	yes	When the upstream group was last edited.

Keys

• Primary key: id

Indexes

• idx_projectid on projectid
• idx_couchdbid on couchdbid
• idx_id on id

The PK already implies an index on id; the explicit idx_id is redundant but harmless.

Relationships

Direction	Other table	Join	Cardinality
outgoing	projects	map_groups.projectid = projects.id	many groups → one project
incoming	maps	maps.groupid = map_groups.id	one group → many maps

No FK constraints in MariaDB; joins are by string match.

Refresh Semantics

A row is upserted when its map-group document is observed in source, and replaced on subsequent observations. There is no archived column — if a group is removed upstream, its row is dropped on the next reindex, but until then any maps.groupid referencing it continues to resolve.

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

Common Queries

List groups on a project

SELECT id, name, creationdate
FROM map_groups
WHERE projectid = 'your_project_id'
ORDER BY name;

Groups with their map counts

SELECT
  mg.id,
  mg.name,
  COUNT(m.id) AS map_count
FROM map_groups mg
LEFT JOIN maps m
  ON m.groupid = mg.id
 AND m.archived IS NULL
WHERE mg.projectid = 'your_project_id'
GROUP BY mg.id, mg.name
ORDER BY map_count DESC, mg.name;

Empty groups

SELECT mg.id, mg.name, mg.projectid
FROM map_groups mg
LEFT JOIN maps m ON m.groupid = mg.id
WHERE m.id IS NULL
ORDER BY mg.projectid, mg.name;

Cross-table queries (audits per map, tickets per map) live on the Maps domain landing.

Pitfalls

• id is project-scoped, not global. Two projects each with a "Building A" group are two separate rows with different id values. Group by name for cross-project rollups, with the usual free-text caveats.
• No FK constraint to maps. A row can be left here with no maps referencing it (an empty group), and conversely a map's groupid can outlive its group row briefly across reindex windows.
• Denormalized groupname lives on maps. When the group is renamed, maps.groupname may lag until each map is re-synced. The authoritative value is here.