5.4 Preparing the Database

Setup the outbox and event history table for use with debezium.

Since we had a brief look at the configuration we are now going to define the outboxevent table. We will also create an event history table consumedevent to ignore events which have already been processed (idempotency).

Outbox Event Table

The EventRouter used by the Debezium Connector expects the outboxevent table to have a certain layout.

In our application configuration application.properties we mostly stuck with the defaults. Therefore, our outboxevent table should look like this.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$ admin=# \d outboxevent;
                            Table "outboxevent"
       Column       |            Type             | Collation | Nullable | Default
--------------------+-----------------------------+-----------+----------+---------
 id                 | uuid                        |           | not null |
 aggregatetype      | character varying(64)       |           | not null |
 aggregateid        | character varying(64)       |           | not null |
 type               | character varying(32)       |           | not null |
 timestamp          | timestamp without time zone |           | not null |
 tracingspancontext | character varying(255)      |           |          |
 payload            | text                        |           |          |

Indexes:
    "outboxevent_pkey" PRIMARY KEY, btree (id)

Task 5.4.1 - Create outbox table with Liquibase

Change the resources/db/changeLog.xml to include the specification for the outboxevent table. Create the table according the description above.

Task Hint

Your configuration for the outboxevent table should look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    <createTable tableName="outboxevent">
      <column name="id" type="UUID">
          <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="aggregatetype" type="varchar(64)">
          <constraints nullable="false"/>
      </column>
      <column name="aggregateid" type="varchar(64)">
          <constraints nullable="false"/>
      </column>
      <column name="type" type="varchar(32)">
          <constraints nullable="false"/>
      </column>
      <column name="timestamp" type="timestamp">
          <constraints nullable="false"/>
      </column>
      <column name="tracingspancontext" type="varchar(255)">
          <constraints nullable="true"/>
      </column>
      <column name="payload" type="text">
          <constraints nullable="true"/>
      </column>
    </createTable>

full source changeLog.xml

Task 5.4.2 - Create table for event history

Since we want to track the events which were already processed we create a table consumedevent. In this table we are going to store the event id and a received timestamp. We can then use this to check if an incoming event has already been processed by just looking up the event id. This will provide us with idempotency for events which are delivered more than once.

As in the previous task, create a table with the following structure in the liquibase config changeLog.xml.

1
2
3
4
5
6
7
8
9
$ admin=# \d consumedevent;
                      Table "consumedevent"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 id       | uuid                        |           | not null |
 received | timestamp without time zone |           | not null |

Indexes:
    "consumedevent_pkey" PRIMARY KEY, btree (id)
Task Hint

Your configuration for the consumedevent table should look like this:

1
2
3
4
5
6
7
8
    <createTable tableName="consumedevent">
        <column name="id" type="UUID">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="received" type="timestamp">
            <constraints nullable="false"/>
        </column>
    </createTable>

full source changeLog.xml