Dealing with partial overlaps in documents

[michielbdejong]

When importing downloaded CSV bank statements, there can be some overlap in time period.
Also when parsing the response body from an outgoing API call, or parsing the request from an incoming API call, there may be partial overlaps.

So maybe merging the sync table and the statements table was not exactly the right thing to do. We may need to have:

• one components table like we have now
• one movements table like we have now
• one statements table like we have now
• one identifiers table that tracks how neighbouring systems identify each of our movements

And maybe also how they identify each of our components?

Tracking the identifiers may be more useful than tracking the source documents, if those source documents are sync messages.

Business documents like invoices do need to be tracked meticulously, of course. But they also have there own identifiers, usually... Maybe our concept of 'statements' should be more like linked data quads and less like sync messages.

Also, each line in a bank statement is linked to at least two movements:

• the inner movement, between bank and user
• the outer movement, between bank and third party
• the implied delivery movement from third party to user budget
• one or (in the case of deprecation) more implied consumption movements from user budget to user expenses

So that gives a "bank statement line" a right of existence, by itself in the statements table, just like an invoice or a timesheet entry.

[michielbdejong]

Maybe we need a way to describe the relationship between a statement and a movement. This could be done by splitting the 'payment' movement type into different types for inner/outer/etc. For 'invoice' we probably need a better word because it's really not the invoice itself, but the value delivery implied by it.

So for instance inner, outer, delivered, consumed, worked.

By making the statements about the database entries and not about the message documents, it will be easier to deal with partial overlap in documents.

[michielbdejong]

The movementID of a statement from a bank statement import should probably be just the inner movement.

[michielbdejong]

We do need a way to remember the relation between the statement and the other movements though!

[michielbdejong]

So maybe we need an intermediate table and maybe the 'type' column should move there
The movement then needs a 'unit' field which it still doesn't have, and there we can distinguish 'hours' versus 'EUR' versus maybe 'various' in the case of delivery-movements that are implied by an invoice, where it is hard or impossible to specify a unit.

[michielbdejong]

Ah, or ideally, the invoice should of course be machine-read and parsed, so the units can be taken from the line items! :)