Accounting Service database - SSO

Accounting Service stores information in these database tables:

Event table ACCT_EVENT to store the incoming events
Daily Accounting table ACCT_DAILYACCOUNTING to store the daily unique users
Monthly Accounting table ACCT_MONTHLYACCOUNTING to store the calculated monthly user counts

These tables are created automatically in the startup with the embedded scripts by the means of Flyway Database migration tool. Flyway holds the migration data in its own table FLYWAY_SCHEMA_HISTORY.

NOTE: If the embedded PostgreSQL database table creation and additional migration scripts are not optimal for your needs you can replace them by extending the Accounting Service configuration with Spring Boot Flyway settings. However, the product has been tested only with the embedded scripts and PostgreSQL database.

Event table details

Event table stores the timestamps in UTC timezone.

Since the release of SSO 8.8.0 with Accounting Service 1.2.x, the column: ACCC_EVENT_DATA has been deprecated in favour of new column: ACCC_EVENT_DATA_JSON. This column stores the event value directly as a string although it is still defined as a text data type in the PostgreSQL database.

Most of the event attributes are stored in JSON format in the column ACCC_EVENT_DATA_JSON (previously: ACCC_EVENT_DATA). Event data depends on the event type but currently only ticket granted events are handled.

Example of ticket granted event data JSON part

{
    "ticketRequestId": "54e3d543997f2d25dbf01a9414a0bd72d0fa7e3291ace8315c6dab71147acd1c",
    "authRequestOrigin": "CN=eidm2,OU=eIDM Services,CN=Ubilogin,DC=test",
    "redirectUrl": "https://localhost:7443/",
    "identityType": "UBILOGIN",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "sessionId": "d74700161d7ea52e990399609fc1e3c016f21f3bbc39f11e256e709b2a503c38",
    "authMethodName": "saml.ap.custid",
    "remoteAddress": "ee6b208b66b8990afd3071b8e96395c3cecd00b1c08a6499e6b8ef62c67c171e",
    "authMethodType": "SAML"
}

This note is applicable only for events created before upgrade to SSO v. 8.8. with Accounting Service 1.2.x

The deprecated ACCC_EVENT_DATA column contains a large object and is defined as text data type in the PostgreSQL database. PostgreSQL stores in the text column an index referring to another table with the actual data. In order to query it you need to use e.g. the following kind of SQL syntax:

SELECT *, 
    convert_from(loread(
         lo_open(accc_event_data::int, x'40000'::int), x'40000'::int), 'UTF-8') 
    AS accc_event_data
FROM acct_event;

See also PostgreSQL Documentation, Large objects (https://www.postgresql.org/docs/9.6/lo-interfaces.html)

For events having ACCC_EVENT_DATA_JSON column populated no such conversion is needed.