Data model

Seven entities, all Hibernate ORM with Panache. Five extend PanacheEntity and inherit a generated Long id; WebAuthnCredential extends PanacheEntityBase because its primary key is the credential id string.

The schema is owned by Flyway, under src/main/resources/db/migration. Hibernate is set to validate, so a mapping that no longer fits the schema fails at boot instead of being quietly tolerated. V1__baseline.sql carries the tables and the constraints; V2__audit_ledger.sql carries the audit table, its hash chain and the triggers that make it append-only.

Dev and test override this: H2 cannot run pgcrypto or plpgsql triggers, so quarkusDev generates the schema from the entities instead. Tests run against a real PostgreSQL started by Dev Services, so the migrations, the triggers and the chain are all exercised.

quarkus.datasource.db-kind=postgresql
quarkus.datasource.jdbc.url=${DB_URL:jdbc:postgresql://localhost:5432/mobilesigner}
quarkus.hibernate-orm.schema-management.strategy=update

Table names come from the @Table annotations, but column names do not use underscores — Hibernate's default implicit naming lowercases the field name, so fileNumber becomes filenumber and currentHolder becomes currentholder. Worth knowing before writing a query by hand. Approval carries no @Table, so its table is approval.

update means Hibernate adds missing tables and columns but never drops or narrows anything. It will not reconcile a renamed column or a changed type, so a schema change that is not purely additive leaves the database in a state Hibernate quietly tolerates.

The schema

erDiagram
  USER_ACCOUNT ||--o{ WEBAUTHN_CREDENTIAL : "credentials"
  GOV_FILE ||--o{ FILE_NOTE : "noting sheet"

  USER_ACCOUNT {
    Long id PK
    String username UK "not null"
  }
  WEBAUTHN_CREDENTIAL {
    String credentialId PK
    bytes publicKey
    long publicKeyAlgorithm
    long counter "incremented per assertion"
    UUID aaguid "authenticator model"
    Long user_id FK "not null"
  }
  GOV_FILE {
    Long id PK
    String fileNumber UK "not null"
    String subject "not null, 500"
    String department "not null"
    String category "not null"
    String priority "not null"
    String status "not null"
    String currentHolder "not null"
    String initiatedBy "not null"
    Instant initiatedAt "not null"
    String body "nullable, 4000"
    String amount "nullable, display string"
    String goNumber "nullable"
  }
  FILE_NOTE {
    Long id PK
    Long file_id FK "not null"
    int noteNumber "not null"
    String authoredBy "not null"
    String designation "not null"
    Instant authoredAt "not null"
    String noteType "not null, GREEN or YELLOW"
    String content "not null, 2000"
    String signatureHash "nullable, 64"
    Instant signedAt "nullable"
  }
  APPROVAL {
    Long id PK
    String username "not null"
    String orderId "not null, matches fileNumber"
    String documentHash "not null, 64"
    Instant approvedAt "not null"
    String assurance "not null"
  }
  AUDIT_EVENT {
    Long id PK
    String eventType "not null"
    String actor "not null"
    String actorRole "not null"
    String targetId "nullable, free text"
    Instant occurredAt "not null"
    String outcome "not null"
    String detail "nullable, 1000"
    String correlationId "not null, 36"
  }
  SIGNING_DEVICE {
    Long id PK
    String serialNumber UK "not null"
    String icType "not null"
    String firmwareVersion "not null"
    String state "not null"
    String provisionedCa "nullable"
    String assignedTo "nullable"
    String certSubject "nullable"
    String certSerial "nullable"
    Instant validFrom "nullable"
    Instant validTo "nullable"
    Instant registeredAt "not null"
  }

How the tables join

Only two associations are enforced by the database. The rest are joined by string convention, which is worth understanding before writing a query against them.

graph LR
  UA["UserAccount"] ==>|"@OneToMany<br/>mappedBy user"| WC["WebAuthnCredential"]
  GF["GovFile"] ==>|"@ManyToOne<br/>optional false"| FN["FileNote"]
  AP["Approval"] -. "orderId = fileNumber<br/><small>no foreign key</small>" .-> GF
  AE["AuditEvent"] -. "targetId, free text<br/><small>no foreign key</small>" .-> GF
  AE -. "targetId, free text<br/><small>no foreign key</small>" .-> SD["SigningDevice"]
  SD -. "assignedTo, a display name<br/><small>no reference</small>" .-> UA
  SD -. "no relationship at all" .-x WC

  classDef strong fill:#e4f4ec,stroke:#0d6b4f,color:#18221d
  classDef weak fill:#f7f8f8,stroke:#cdd4d1,color:#38443d
  class UA,WC,GF,FN strong
  class AP,AE,SD weak

Solid arrows are foreign keys. Dotted arrows are conventions the application maintains and the database does not check. The crossed link is the one to keep in mind: SigningDevice and WebAuthnCredential are unrelated tables, so the device inventory is descriptive rather than something a ceremony consults.

GovFile

Table gov_file. The file as a unit of work.

priority and status are String, not @Enumerated. The valid values live in three unrelated places — FileResource.priorityRank(), the TypeScript union types in lib/api.ts, and the tone maps in badge.tsx.

Value set	Members
priority	MOST_IMMEDIATE, IMMEDIATE, ROUTINE
status	PENDING, APPROVED, REJECTED, RETURNED
category	Policy directive, Financial sanction, Tender acceptance, Revenue order

amount is a pre-formatted display string such as ₹842.7 Cr. It cannot be summed, compared or converted.

Finders:

GovFile.findByStatus(String status)      // list("status = ?1 order by initiatedAt desc", status)
GovFile.findByFileNumber(String number)  // find("fileNumber", number).firstResult()

FileResource.list() does not use findByStatus — it calls listAll() and filters in a stream, because it combines three optional predicates with a custom sort.

FileNote

Table file_note. One entry on the noting sheet.

noteType carries the NIC eOffice convention. A GREEN note is permanent and part of the record; a YELLOW note is a draft. lib/format.ts renders these as Permanent note and Draft note so an officer is not asked to remember a colour code.

signatureHash and signedAt are the binding between a noting and a cryptographic act. They are set together or not at all — FileResource.act() assigns both from the same Instant now, and the seeder's signedNote() helper does the same.

noteNumber is computed at write time from the current maximum for the file:

private static int nextNoteNumber(Long fileId) {
    return FileNote.findByFile(fileId).stream().mapToInt(n -> n.noteNumber).max().orElse(0) + 1;
}

There is no unique index on (file_id, note_number); contiguity is maintained by this method inside the @Transactional action, and in practice a file sits with one officer at a time.

Finder:

FileNote.findByFile(Long fileId)   // list("file.id = ?1 order by noteNumber", fileId)

AuditEvent

Table audit_event. The activity ledger.

targetId is nullable and is genuinely null for AUTH_SUCCESS rows, which have no file or device to point at.

correlationId is a fresh UUID.randomUUID() per event, assigned inside record(), so it is unique per row.

The single write path:

public static AuditEvent record(String eventType, String actor, String actorRole,
                                String targetId, String outcome, String detail) {
    var event = new AuditEvent();
    // …
    event.occurredAt = Instant.now();
    event.correlationId = UUID.randomUUID().toString();
    event.persist();
    return event;
}

occurredAt is always Instant.now(), so callers cannot backdate an event. DemoDataSeeder bypasses record() and builds rows by hand precisely so it can set historical timestamps.

Event types written at runtime are FILE_APPROVED, FILE_REJECTED, FILE_RETURNED and SIGNATURE_VERIFIED. AUTH_SUCCESS and CREDENTIAL_REGISTERED appear in the seed data.

SigningDevice

Table signing_device. The hardware inventory.

The four states describe a lifecycle:

graph LR
  R["REGISTERED<br/><small>known to the platform</small>"] --> P["PROVISIONED<br/><small>certificate issued</small>"]
  P --> A["ACTIVE<br/><small>bound to an officer</small>"]
  A --> V["REVOKED<br/><small>withdrawn</small>"]

  classDef ok fill:#e4f4ec,stroke:#0d6b4f,color:#18221d
  classDef mid fill:#e8f0f9,stroke:#1d4e89,color:#18221d
  classDef off fill:#eef0f2,stroke:#4a5568,color:#18221d
  classDef bad fill:#fdeaea,stroke:#8b2020,color:#18221d
  class R off
  class P mid
  class A ok
  class V bad

The lifecycle is descriptive. DeviceResource exposes a read endpoint only, so states are whatever the seeder wrote; there is no transition code and no write path.

provisionedCa is null for a device that is REGISTERED but not yet issued a certificate — MS-2026-D4A6B520 in the seed data is exactly that case.

Finder:

SigningDevice.findByState(String state)   // list("state = ?1 order by serialNumber", state)

Approval

Table Approval — the default name, since it carries no @Table. The digest-to-identity binding that makes verification possible.

documentHash has no unique index, so the same digest can appear more than once, and VerifyResource resolves it with firstResult(). There is no index on the column either, so verification is a full scan; immaterial at demo scale.

assurance is the constant string WebAuthn user verification at both write sites. It does not record which authenticator was used or its AAGUID.

orderId holds the file number, not the numeric file id, which is what lets /api/verify name the order in its output without a join.

UserAccount

Table user_account. The officer identity: a username, and a collection of credentials.

There is no display name, designation, department or role column. Every designation shown anywhere in the platform is either a string on a FileNote or the Approving Authority constant in FileResource.

Rows are created on first enrolment, inside DemoWebAuthnUserProvider.store():

var user = UserAccount.findByUsername(record.getUsername());
if (user == null) {
    user = new UserAccount();
    user.username = record.getUsername();
    user.persist();
}

The credentials collection has no cascade, so deleting a UserAccount would leave its credentials behind.

WebAuthnCredential

The public half of the officer's key pair. Extends PanacheEntityBase because the credential id is the primary key.

No private key material is stored. That is the whole point — the private key never leaves the authenticator.

The entity converts to and from the Quarkus record type:

public WebAuthnCredentialRecord toRecord() {
    var data = new WebAuthnCredentialRecord.RequiredPersistedData(
            user.username, credentialId, aaguid, publicKey, publicKeyAlgorithm, counter);
    return WebAuthnCredentialRecord.fromRequiredPersistedData(data);
}

counter is updated on every successful assertion by DemoWebAuthnUserProvider.update(). A counter that fails to increase is the standard signal of a cloned authenticator; the Quarkus extension performs that check, and the seeded audit trail contains a matching FAILURE row to show what it looks like.

aaguid identifies the authenticator model and would be the basis for a policy such as "only FIPS-certified keys from this vendor list". It is stored but not currently read.

One credential per officer

store() deletes every existing credential for the username before persisting the new one:

var existing = WebAuthnCredential.findByUsername(record.getUsername());
for (var old : existing) {
    old.delete();
}
new WebAuthnCredential(record, user).persist();

This enforces a single active key per officer, which keeps the identity-to-key mapping unambiguous. The consequence is that enrolling a second key replaces the first rather than adding a backup, so re-enrolment is the recovery path for a lost key.

Finders:

WebAuthnCredential.findByUsername(String username)     // list("user.username", username)
WebAuthnCredential.findByCredentialId(String id)        // findById(id)