audit_signatures

One row per sign-off collected on an audit. An audit can require multiple signatures (e.g. inspector + project lead + client), and each sign-off captures the signer's name and the timestamp.

Source Mapping

CouchDB document type: an "audit" document — rows are produced by asyncconvert.py's audits_signatures(obj) handler iterating obj.signature[*] (note the singular field name in source).

Column	CouchDB source	Notes
audit	obj.id	The audits.id this row belongs to.
signee	obj.signature[i].name	The signer's display name — not their email. See Pitfalls.
date_signed	obj.signature[i].signedDate	Parsed via parseDate(...).
couchdbid	obj.couchDbId	Legacy CouchDB document identifier (the audit's).

Column Reference

Column	Type	Nullable	Description
audit	VARCHAR(255)	yes	Foreign-key reference to audits.id.
signee	VARCHAR(255)	yes	The signer's display name (free text — see Pitfalls).
date_signed	DATETIME	yes	When the sign-off was captured.
couchdbid	VARCHAR(255)	yes	Legacy CouchDB identifier of the audit document.

Keys

• No primary key, no unique constraint. A signer can sign once per audit (in practice), but the schema does not enforce it. Identity in practice is (audit, signee, date_signed).

Indexes

• idx_parent on audit

There is no index on signee or date_signed. Queries that find "all audits this person signed" will scan the table.

Relationships

Direction	Other table	Join	Cardinality
outgoing	audits	audit_signatures.audit = audits.id	many sign-offs → one audit

There is no projectid column on this table — to filter by project, join through audits first. There is also no reliable join to users because signee is a name, not an email.

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

Refresh Semantics

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

1. Issues DELETE FROM audit_signatures WHERE audit = <audit_id> (when delete tracking is enabled).
2. Iterates obj.signature[*] and inserts one row per sign-off.

Reading during an in-flight reindex of an audit may briefly return zero rows for that audit.

A reindex with type=audit (or a full reindex) rebuilds this table as part of the audit reindex.

Common Queries

All signatures on an audit

SELECT signee, date_signed
FROM audit_signatures
WHERE audit = 'your_audit_id'
ORDER BY date_signed;

Audits signed by a given person (by name)

SELECT
  a.id,
  a.name,
  s.date_signed
FROM audit_signatures s
JOIN audits a ON a.id = s.audit
WHERE s.signee = 'Jane Doe'
ORDER BY s.date_signed DESC;

Names are free text — see Pitfalls.

Sign-off latency from completion to first signature

SELECT
  a.id,
  a.name,
  a.completiondate,
  MIN(s.date_signed)                                  AS first_signature,
  TIMESTAMPDIFF(HOUR, a.completiondate, MIN(s.date_signed)) AS hours_to_first_sign
FROM audits a
JOIN audit_signatures s ON s.audit = a.id
WHERE a.projectid     = 'your_project_id'
  AND a.completiondate IS NOT NULL
GROUP BY a.id, a.name, a.completiondate
ORDER BY hours_to_first_sign DESC;

Pitfalls

• signee is a name, not an email. Unlike every other "user" reference in the schema (audits.author, audits.responsible, audit_roles.email, tickets.responsible, etc.), this column stores signature[i].name — a free-text display name. There is no reliable join to users.email from here. If you need the user account, look up the email separately or by name + project.
• No projectid column. Filter by project requires joining through audits first. Don't try to use this table standalone for project-scoped queries.
• No primary key, no unique constraint. A duplicate signature (e.g. the same name signing twice with different timestamps) is preserved as two rows. Reading during an in-flight reindex may briefly return zero rows for that audit.
• date_signed is whatever the source said. No clamping or reconciliation against audits.completiondate — it can occasionally predate completion (e.g. when an inspector pre-signs in the field).
• No FK constraint. Joins to audits.id are by string match.