flash-oss / medici Goto Github PK

Double-entry accounting is so highly relational by design, why not a SQL db?

Hi ,

Thank you for helping to find the Balance. However, I would like to know if my AccountReceivable:GivenAccount has a debit value of 11,371.66.

But when I retrieve the balance I get a negative number in the account balance query = -11,371.66

Can I know if a Negative Balance always means "debit value" when we Querying Account Balance?

I have the following code

  const myBook = new book("test");

  let entry = myBook.entry("This is a test entry");

  entry.debit("Assets:Cash", 99.9, {});
  entry.credit("Income", 99.8, {});

  entry.commit().then(journal => console.log(journal));

which leads to a valid journal entry instead of raising an INVALID JOURNAL exception, as expected.

{ __v: 0,
  book: 'test',
  datetime: 2018-03-02T10:36:24.244Z,
  _id: 5a992928b7323207c3713991,
  approved: true,
  voided: false,
  _transactions: [ 5a992928b7323207c3713992, 5a992928b7323207c3713993 ],
  memo: 'This is a test entry' }

Medici is slow when the number of records reach 30k

I think the Readme is misleading.

I'm assuming there is no performance issue over 30k record when running index.

Is that correct?

related to #12

When voiding a transaction the void pair keeps unvoid, so you could not filter only unvoids.
EXAMPLE:

var Libro = new medici.book('Libro');
Libro.entry("test").debit("Activos:Banco", 5000).credit("Gastos:Proyecto", 5000).commit()
    .then( function (journal) {
        // ill ignore the returned journal
        Libro.ledger("Activos").then( function(docs) {
            // Now ill void the first item
            Libro.void( docs.results[0]._journal, "Error" ).then( function () {
                // ill check the results
                Libro.ledger("Activos").then( function(docs) {
                    console.log(docs);
                });  
            });
        });
});

How can I specify the connection to use ?

For example, when connection is achieved by mongoose.createConnection(...) vs mongoose.connect(...).

The project I am working on makes use of connections returned from the createConnection method, and not via the standard mongoose connection pool.

Cant find any documentation or outline in your readme on where the connection can be specified

trying this example:

const startDate = moment().subtract("months", 1).toDate(); // One month ago
const endDate = new Date(); // today

const transactions = await myBook.ledger({
  account: "Income",
  start_date: startDate,
  end_date: endDate
});

I get:

CastError: Cast to date failed for value "Invalid Date" at path "datetime" for model "Medici_Transaction"
    at model.Query.exec (/Users/anentropic/Documents/Dev/Work/balance-demo/node_modules/mongoose/lib/query.js:4437:21)
    at Book.ledger (/Users/anentropic/Documents/Dev/Work/balance-demo/node_modules/medici/src/book.js:212:30)
    at main (/Users/anentropic/Documents/Dev/Work/balance-demo/pay_periods.js:32:35)
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  messageFormat: undefined,
  stringValue: '"Invalid Date"',
  kind: 'date',
  value: Invalid Date,
  path: 'datetime',
  reason: AssertionError [ERR_ASSERTION]: The expression evaluated to a falsy value:

    assert.ok(!isNaN(value.valueOf()))

      at castDate (/Users/anentropic/Documents/Dev/Work/balance-demo/node_modules/mongoose/lib/cast/date.js:13:12)

Same if I use Date for both with no moment.js stuff.

It turns out the fix is to pass start_date and end_date as strings instead of Date instances, i.e.:

  const transactions = await myBook.ledger({
    account: "Income",
    start_date: "2021-01-01T12:00:00",
    end_date: "2021-05-01T08:00:00"
  });
  console.log(transactions);

...works fine.

This is with medici-4.0.1 mongoose-5.12.7 and MongoDB 4.2

I think this line is wrong

Should be actually something like () => (new Date())

Is it possible to set the timestamp manually for a journal entry ?

I just reviewed this project. It's 5 stars and great! Almost everything is documented but what got me stuck there was a starting point or a startup demo. This would be of great help to others to kick things off immediately.

Is there an example project of how this is used? I'm debating between this and using SQL.

I'm assuming 6.0.0 brings support for mongodb version 6, and would like to upgrade to it. It would be great to have a changelog in the readme for 6.0.0 like there is for other versions that has the breaking changes mentioned.

The balance queries take 100 - 400 ms average. The MongoDB Cloud Performance Adviser shows these fields are good candidates for indexing:

account_path.0: 1
account_path.1: 1
account_path.2: 1
book: 1
approved: 1

The Medici v2.1 should be released and set the indexes by default.

First of all, thank you for the project, I'm learning a lot from it. I was interested on implementing the same idea on a SQL database, however I had some doubts.

What makes me wonder is if it makes sense to create a collection (table if SQL) for each account. e.g. a collection for Assets:Cash:USD, one for Assets:Cash:EUR and so on. Was this ever considered on this project? what would be the drawbacks? Because as I see having all on medici_transactions tends to make the collection to get large in size too quickly, and that brings the question of whether this would be an issue -mostly performance-wise- when we query this collection or inspect the data.

@koresar

I made some changes to the typescript branch. I removed node 10 support, as it is EOL and added node 16 as it is current LTS.

The branch itself uses mongoose 6. Maybe it is better to make it a peerDependency? But this should resolve #30

I also added an optional Options parameter to "sessionable" methods to pass a mongo session. I think there is an issue with the pre save hook regarding sessions. Should resolve #23

The API itself is not breaking. But removed node 10 support and usage of mongoose 6 is definitely breaking change and not avoidable.

I personally dont use prettier, so I dont have any configuration which I could add to it.

I thought about this issue multiple times, and these were my "simple" solutions.

1. Account Table
   All the transactions will be summed up in an account document. Potential issue is the writeLocks on heavy used accounts.

2. Create a balanceSumUp collection (UPD: we have implemented this solution)
   The actual issue of balance is that the operation is O(N) where N is the amount of transactions assigned to an account. So probably the easiest solution would be to make a balance call and store the value into the balanceSumUpTable. So the BalanceSumUp would contain the last transactionId of the balance method, the sum of the balance, and an expiration value, e.g. daily. So what happens is, thay we first search for the balance in the dailyBalanceSumUp Collection. If we find it, we determine the balance and the transaction id we stored. We then do the balance but the _id has to be bigger than the last _id of balanceSumUp. Probably needs an appropriate index. But what happens is that if we have balance of an Account with 1000000 transactions we would not read 1000000 transactions but e.g. the last 1000 of the 1000000 transactions. Thus reducing the Runtime to O(n+1), where n is the amount of additional transactions since the persisted balance. If you set the expires (when the document is automatically deleted by mongo) to 1 day than you would have only once a day the slow down to run the whole balance. Or you set the expires (mongo expires) to 2 days and add a second expires to 1 day, but this expires does not result to a automatically deletion but indicates the freshness of the balance. So you take the last persisted balance, check if it is fresh enough, if so you calculate the balance since then. If it is not fresh, you persist the new balance to the collection, were you update freshness and expires. So you have a potential write conflict when writing to the persisted balance resulting in a retry to persist the balance?! But only once a day.
   Or you don't expire at all and do just the freshness check.

This would not make it necessary to store additional information to the transactions. And it should be still faster than traditional balance.

Hello,

I've been looking for a double-entry accounting system package to use in my new project, and came across medici.
The project looks interesting and I am trying to start building a POC using it.

However, I cannot find a way to specify the configuration for mongodb to use, nor does it seem to work out of the box (without any specified configuration).

I keep getting the below error, when I try to run the example code:

TransactionError: Failure to save journal: Operation medici_transactions.insertMany() buffering timed out after 10000ms
at Entry.commit in medici/build/Entry.js — line 124

The code I'm using is as below:

const { Book } = require("medici");
const journal = new Book("FY 2022-23");
const amount = 123456.78;
await journal.entry("Sample Entry")
    .debit("Incomes:Sales", amount, {narration: "Debit towards Sample Entry"})
    .credit("Expenses:Purchases", amount, {narration: "Credit towards Sample Entry"})
    .commit();

Could you please help me identify what am I doing wrong here?

Looking for a volunteer to do it.

• Drop node 6 support,
• replace .then() with async/await

Hi Medici team,
It seems that the balance API doesn't return the correct values if there is a cached balance entry in balance_snapshots.
If the start_date and end_date are values before the last transaction Id in the snapshot, the balance API just returns the balance in the snapshot.
link

I suggest that we should also maintain a start_time and end_time in the balance snapshot and check if the range query can be satisfied by the snapshot. Else recalculate accordingly.
Happy to raise a PR for the same if we agree on a solution.
Or please suggest a work-around which doesn't turn the caching feature completely off.

Thanks

Hi Medici team et. al., thank you SO MUCH for creating this package - super helpful!! Asking for tips for best approach / recommendation on creating a journal entry with a future posting date.

Consider this scenario: create a journal entry with a future posting date, say 14 days (parameterized) later.

As of today:
#1. Journal and Transaction have datetime and timestamp attributes at the journal entry time.
#2. Book.ledger({start_date: startDate, end_date: endDate}), Book.balance({start_date: startDate, end_date: endDate}).... are based on entry date as well.

What we need is to have extra field so-called posting-date.
On #1-enhanced: Journal and Transactions have extra posting date attributes.
On #2-enhanced: ability to query ledger and balance based on posting date.
#2a. If query as of today ==> query returns no data since posting dates are in the future.
#2b. If query as of 14 days later ==> query returns the journal and transactions since within the posting dates.

Thoughts / tips / help? Thank you in advance!!

Hello @Uzlopak

I am going to publish the new v7.0. The only breaking change - other default indexes. See the latest commits.

Today was a hugely frustrating day for me. Turns out all the default indexes were an absolute horsedump.

After the improvements not only all our ledger queries started to take milliseconds rather than seconds, but also we decreased the index RAM usages by x3 times.

So, this message is mostly FYI. Make sure you're ready for the release.

Hi, I want to find the balance for a previous date. Is this possible?

Eg. I need to find the Accounts Receivable balance as of Jan 31st, 2024.

Please advise.

Currently, journal/transactions are not added/update with atomic, database-level transactions. This can lead to inconsistency if the database crashes in the middle of a transaction.

Hi , can you let us know if there is a way to add Opening Balance to a book? I belive then we can't add a double entry. Please advice.

hey guys @maintenairs.

there are been a couple of merged PR that we'd like to use, but medici has not been released following those PR merged. is that something you can do?

Hi, nice library, but money should not be stored in floats - this is just very risky and a great pitfall.

Maybe you could implement dinero?

https://v2.dinerojs.com/docs

Thank you for your hard efforts, it's an amazing project

https://www.prisma.io/ - supports a lot of databases and it would be amazing to use such ORM and make this project independent from the underlying database

Entry.write() no longer works (Use commit insteat??)

@koresar
Thank you for this module.

We have created a test program that demonstrates this. We have introduced two custom properties: _businessId and _assetId.

• Without using a custom schema retrieving a book balance using these custom properties as keys returns the correct result.
• When using a custom schema (line 15 in app.js is uncommented), retrieving book balance without using any of the custom properties works correctly (see _retrieveBalance() in app.js), but retrieving balance using either one or two of the custom properties as keys returns 0 (see _retrieveBalanceWithCustomProperties1() and _retrieveBalanceWithCustomProperties2() in app.js)

For the 3 test cases the correct balance should be

• -2000 for _retrieveBalance()
• 2000 for both _retrieveBalanceWithCustomProperties1() and _retrieveBalanceWithCustomProperties2()

To run without custom schema defined (ensure line 15 in app.js is commented out)
Tun run with custom schema defined (ensure line 15 in app.js is uncommented)

Possibly we are doing something wrong in our schema definition. Thanks in advance for your help.

medici-v5.zip

Please sayme if this project its compatible with NIIF international norms of IFRS?

Thanks!

Hi is there a way to get a list of all accounts in a book?

I want to add custom field as a status by default pending and want to update that to success or failed. is there any way i can achieve this?

If you were to implement a multi-currency version of Medici, how would you do it?

I guess

.debit('Assets:Cash', 1000, {meta}) would need another parameters, ie: .debit('Assets:Cash', 1000, {meta}, "USD")

A transaction would still equal out with multiple currencies. ie Alice sending 10 USD for 12 EUR to Bob:

.credit('Alice', 10,  {}, 'USD')
.debit('Bob", 10, {}, USD')
.debit('Alice', 12,  {}, 'EUR')
.credit('Alice, 12, {}, 'EUR')

Now the issue would be that, if we try to fetch a balance like this, we will have currency co-mingled.

ie:

const balance = await myBook.balance({
  account: "Assets:Accounts Receivable",
  client: "Joe Blow"
});

So we would need to have a mandatory currency field here. And filter that out.

I'm wondering what the most efficient way to do it would be?

Simply adding a currency field in transaction, and modifying the balance function? Any other idea?

Hello,

I keep getting the below error, when I try to run the example code:

TransactionError: Failure to save journal: Operation medici_transactions.insertMany() buffering timed out after 10000ms
at Entry.commit in medici/build/Entry.js

The code I'm using is as below:

const { Book } = require("medici");
const journal = new Book("My Book");
const amount = 10000;
await journal.entry("New Entry")
.debit("Incomes:Sales", amount, {narration: "Debit Entry"})
.credit("Expenses:Purchases", amount, {narration: "Credit Entry"})
.commit();

Could you please help me identify what am I doing wrong here?

Hello
Great job!
Does Medici supports multiple VAT?
Thanks

The double entry principle which states that:

for every debit entry, there must be a corresponding credit entry.
was added.

I read the document carefully but still can't find the introduction how to provide my mongodb uri to medici package?

Actually, my piece of code below throw the error Failure to save journal: Operation `medici_transactions.insertMany()` buffering timed out after 10000ms

const myBook = new Book("MyBook", { precision: 18 });
const journal = await myBook
  .entry("Received payment")
  .debit("Assets:Cash", 1000)
  .credit("Income", 1000, { client: "Joe Blow" })
  .commit();
const { balance } = await myBook.balance({
    account: "Assets:Accounts Receivable",
    client: "Joe Blow",
  });
console.log("Joe Blow owes me", balance);

Is there any one facing problem like me?

We push the transaction into the array and later we add meta to the object

https://github.com/flash-oss/medici/blob/master/src/entry.js#L104
https://github.com/flash-oss/medici/blob/master/src/entry.js#L105

In credit we first add meta and later we push it to transactions.

Probably this is no problem, as we have an Object reference so we still manipulate the object in the array. But I think it is still a code smell.

@koresar

The failed unit test made me thinking. Despite we lost the validation functionality this actually means that you could overwrite the timestamp and set it something in the future or in the past, which would actually violate the "rule of good bookkeeping". Also could mean that a persisted balance could be breaken, by setting a debit into very past. Didn't thought of it before.

Implicitly allowing to set the timestamp arbitrary is probably not good.

Maybe add an timestamp attribute to Entry.ts and use it when creating each transaction, thus avoiding diverging timestamps by milliseconds and then in the line before we push the transaction into the transactint array assign the Entry timestamp to the transaction timestamp.

How are you handling floating point precision in this library. Seems like you're just storing a javascript Number type. I am pretty sure this will lead to rounding errors when calculating balances.

http://floating-point-gui.de/

@koresar

@adam2k and I worked on medici and we modified it accordingly to fix the sec. vuln. of mongoose.

Can you please make a release?

I am trying to run an example with MongoDB back end.

` const bookDb = new book("MyBook");

let journal = await bookDb
  .entry("Test Entry")
  .debit("Assets:Receivable", 500, { clientId: "12345" })
  .credit("Income:Rent", 500)
  .commit();`

But I don't see the commit working.
Also is there a way to specify the DB name so as to support multi tenant solution.

An working example on MERN stack would be of great help.

I have tried running the code snippet in the README but it says that "write is not a function". The code i tried is:

const { book } = require('medici');

const myBook = new book('TEST');

myBook.entry('cool')
.debit('Assets:Cash', 1000)
.credit('Income', 1000, { client: 'Sameer' })
.write().then(result => {
console.log(result);
});

It seems invoiced are being voided for the transaction collection but not marked as voided on the journal collection. I'll make more tests and provide a PR if that the case.

Hi,

not sure if this library is still actively maintained. I believe I have come across a bug where transactions which have meta tags are not voided correctly. The extra ledger entry which is created to reverse the original transaction should have the same meta data as the original entries. if this is not the case - then balance queries based on meta tags are incorrect when a transaction has been voided.

The commit below shows a fix and updated test case. I also upgraded the version of a few libraries.

mos-flash@729253b

I'm fairly new to JS and coffee script so feedback on changes is welcome.

Upgrading to mongoose 6.x is breaking medici. All the operations return buffering timeout errors.

Hello,

I've been playing around your lib and like it a lot. I was wondering how I would go about adding a method to get the journal entries and populate those with the debits and credits. I have a usecase where I would need to upload the entries to an external accounting system.

I looked into the source and saw the this.journalModel = mongoose.model('Medici_Journal');

and of course have used the ledger() method.

Would I just follow the same style of the ledger method and populate the ID's normally in mongodb?

Kindly advise, I can even put in a PR if this feature is warranted. :)