code.vegaprotocol.io/vega@v0.79.0/datanode/docs/schema_design_orders.md (about)

     1  # Orders
     2  
     3  These are a bit more complicated because there are two types of updates to orders:
     4  
     5  - Updates initiated by the user, by sending an amend transaction
     6  - Updates that happen in core, because the state of the order has change in some way (e.g. due to a fill)
     7  
     8  To accommodate this, the order table is unique in having a `version` column *which is incremented only when the user amends his order by submitting a transaction.* It is ******not****** incremented when the state of the order is changed internally by Vega.
     9  
    10  ```sql
    11  CREATE TABLE orders (
    12      id                BYTEA                     NOT NULL,
    13      version           INT                       NOT NULL,
    14      vega_time         TIMESTAMP WITH TIME ZONE  NOT NULL,
    15      ....
    16  );
    17  ```
    18  
    19  Now we have two concepts of *version*:
    20  
    21  - The *user* concept of version whereby each time he amends an order the version number goes up.
    22  - The *system* concept of version; the latest of which is the row with the highest `vega_time`
    23  
    24  ## Worked Example
    25  
    26  Lets imagine the `orders` table looks a bit like this:
    27  
    28  |  | id | version | status | vega_time |
    29  | --- | --- | --- | --- | --- |
    30  | user submits order | 0x01 | 1 | ACTIVE            | 2023-01-01 00:01 |
    31  | order gets partially matched | 0x01 | 1 | PARTIALLY_FILLED  | 2023-01-01 00:02 |
    32  | user amends order price level | 0x01 | 2 | PARTIALLY_FILLED  | 2023-01-01 00:03 |
    33  | rest of order gets filled | 0x01 | 2 | FILLED            | 2023-01-01 00:04 |
    34  
    35  We have three views over this data:
    36  
    37  `orders_current`
    38  
    39  For each distinct `id`, the set of rows with the highest `vega_time`
    40  
    41  | id | version | status | vega_time |
    42  | --- | --- | --- | --- |
    43  | 0x01 | 2 | FILLED            | 2023-01-01 00:04 |
    44  
    45  `orders_current_versions`
    46  
    47  For each distinct `(id, version)` pair, set of rows in the table with the highest `vega_time`. 
    48  
    49  It shows you the latest *system versions* for all the *user amended versions* of an order. 
    50  
    51  | id | version | status | vega_time |
    52  | --- | --- | --- | --- |
    53  | 0x01 | 1 | PARTIALLY_FILLED  | 2023-01-01 00:02 |
    54  | 0x01 | 2 | FILLED            | 2023-01-01 00:04 |
    55  
    56  `orders_live`
    57  
    58  The set of orders which are actively trading or parked. We maintain it as a real table with triggers for performance reasons, but it is equivalent to 
    59  
    60  ```sql
    61  select * from orders_current where status in (ACTIVE, PARKED)
    62  ```