Database tables

This page describes a selection of tables that are often needed for analysis, support and connecting surrounding systems. The overview of the schemas is under Database. All tables have an Id column; most also have TenantId for the tenant.

biz.IrCases

One entry per document with the header data of the invoice.

Column	Meaning
`ExternalId`	Document number or barcode for users. Does not have to be technically unique.
`ProcessId`	Process of the document (`conf.Processes`), for example overhead costs or materials management.
`CurrencyCode`	Currency. For every currency used there should be an exchange rate in `Mstr.CurrencyExchangeRates`.
`DocDate`	Invoice date.
`ImportDate`	Time of import.
`SupplierId`	Supplier (`Mstr` master data).
`GrossAmountTotal`	Total gross amount.
`EsrAccount`, `EsrReference`, `EsrCodeLine`	Account, reference and coding line of a payment slip. For QR invoices the data is in `PaymentReference` and `QrCodeContent`.
`LastInitializedNodeId`	Current workflow step.

biz.IrTasks

Tasks in the workflow. Each task belongs to a document and is assigned to a user or a role.

biz.IrItemLines

Lines (accounting lines) of a document with account, cost centre and amount.

biz.IrHistory

History of a document: who performed which step and when.

biz.IrMessages

Messages and comments on a document (columns Message, IrCaseId, CreatedUserId, CreatedDateUtc).

biz.VisaRecord and biz.VisaReject

Given and rejected approvals per line (IrItemLineId), with user (UserId), level and, for rejections, the reason (Reason).

biz.OutOfOffice

Absences of users (UserId, StartDateUtc, EndDateUtc).

biz.IrEmail

Queue of the emails invoiceR sends (ReceiverEmail, Subject, SentDate). If SentDate is empty, the email has not been sent yet.

biz.IrSynonyms

Alternative spellings (SourceText, AlternativeText), for example for recognizing terms.

biz.Documents

One entry per file invoiceR has stored. The PDF itself is in the storage, not in the database.

Column	Meaning
`FileName`	Original file name at upload.
`RepositoryHandle`	Identifier of the file in the storage, for example a file path or the archive document ID.
`DocumentType`	`1` invoice, `2` attachment, `3` report, `4` document.
`DocumentRepositoryId`	Storage used (`conf.DocumentRepositories`).
`IrCaseId`	Related document.

The columns Name, MimeType, Description, FileSize and IconType are currently not evaluated.

biz.DocumentRecognitionStates

One entry per file sent to a text recognition service (for example Parashift).

Column	Meaning
`State`	`1` registered, `2` in progress, `3` finished, `4` error.
`DocumentRecogniserId`	Text recognition service used (`conf.DocumentRecognisers`).
`ServiceHandle`	Identifier of the file at the recognition service, for later status queries.
`LastLog`	Last message about the recognition.
`StartTimeUtc`, `EndTimeUtc`	Start and end of the recognition.
`IrCaseId`	Related document.

biz.IrCaseReadRights

Defines which user may open which document in the archive. If there is an entry for user and document, opening is allowed. The table can become very large. The entries are calculated by a recurring job with the stored procedure dbo.CalculateCaseReadRightsForUser.

Column	Meaning
`IrCaseId`	Document.
`UserId`	User.
`IrCaseReadRightType`	Reason for the entry: `1` history, `2` task owner, `3` assignment target, `4` approval target. The standard procedure always sets `1`.
`CreatorId`, `Context`	Optional, to distinguish different sources. Currently not evaluated.

conf.DocumentRepositories

Connected storages and archives. Described under Document storage and archive.

conf.IrRecurringJobs

Recurring jobs. Described under Recurring jobs.

conf.IrReport

Capture cards on the Reports page. Described under Setting up document capture.

conf.IrManagers

Configuration of background components as JSON in Configuration, for example for the email import (see Mailbox connection).

conf.FolderWatchers

Folders from which invoiceR imports files as new documents (WatchPath, Enabled). Further columns define default values for the documents (DefaultMandatorCode, DefaultProcessType, DefaultInvoiceType, DefaultSupplierCode) and whether files are split. For splitting a PDF into several documents by separator pages, DocumentSplitting contains a JSON:

{
  "SplitDocuments": true,
  "DocumentSeparators": [ "InvoiceR" ]
}

conf.AccountingFieldButtons

Additional buttons on individual fields in the accounting form (Name, Location, OptsIcon, OptsOnClick, AccountingFieldId, EnabledPermission).

imp.IrCaseImports

Interface for surrounding systems that deliver new documents. The surrounding system writes a row, and invoiceR takes it over as a document shortly afterwards.

IndexedDate and ProcessedDate stay empty (NULL) on insert. invoiceR sets IndexedDate when it starts the import and ProcessedDate once the document has been created successfully. If ProcessedDate is still empty after a few minutes, the reason is in the log of the import job in the Hangfire dashboard.
Pass values that were not recognized as NULL, not as an empty string.

Column	Meaning
`TenantId`	Tenant, usually `1`.
`ExternalId`	Document number. By agreement with enio it can be empty; the database then assigns a number.
`MandatorCode`	Company code or client in the ERP.
`ProcessType`	`GK` for overhead costs (default), `MM` for invoices with an order reference.
`DocFilePath`	Location of the PDF: file path, UNC path or address with a scheme such as `https://…`.
`DocDate`, `ImportDate`	Invoice date and import time.
`CurrencyCode`	Currency, for example `CHF`.
`SupplierCode`	Supplier number.
`GrossAmountTotal`	Total gross amount. Sufficient if no VAT needs to be shown.
`GrossAmount1`…`4`, `NetAmount1`…`4`, `VatAmount1`…`4`, `VatRate1`…`4`	Amounts per VAT rate. For one rate only fill the fields ending in `1`, for two rates also `2`, and so on.
`InvoiceType`	`R` for invoice.
`OrderNumbers`, `DeliveryNumbers`	For `MM`: recognized order and delivery numbers as a JSON array, for example `["B-2910","B-1001"]`.

INSERT INTO imp.IrCaseImports
  (TenantId, ExternalId, MandatorCode, ProcessType, DocFilePath, CurrencyCode,
   DocDate, ImportDate, SupplierCode, GrossAmount1, NetAmount1, VatAmount1, VatRate1,
   GrossAmountTotal, InvoiceType)
VALUES
  (1, N'2026-000123', N'100', N'GK', N'\\<server>\Import\invoice-123.pdf', N'CHF',
   '2026-05-28', CURRENT_TIMESTAMP, N'10020', 108.10, 100.00, 8.10, 8.1,
   108.10, N'R');

imp.IrOrderImports

Order and delivery references from a surrounding system (ExternalIrCaseId, ExternalOrderId, OrderDate, ExternalDeliveryId, DeliveryDate). invoiceR sets ProcessedDateUtc after taking them over.