Deck: Provide "Changes since X" in the API

[desperateCoder]

Is your feature request related to a problem? Please describe.
We are currently about to write an Android-App for Deck, and i am thinking about a efficient way to synchronize all the data from the server since my last sync.

Describe the solution you'd like
I came to the conclusion it would be very convenient to have an endpoint that tells me about all the changes in boards, stacks and card, just anything i'd might need to know since a given date X (last sync-timestamp)

Describe alternatives you've considered
if you have any better ideas or if i am missing something, please tell me. :)

Additional context
What i was thinking of is something in this (pseudocode-)manner:

select 'boards' as what, id as which_one from boards where last_changed > X
union all
select 'stacks' as what, id as which_one from stacks where last_changed > X
union all
[...anything else...]

I don't know how it affects performance, but if i would get all the data, not only the IDs, i could avoid a bunch of API-calls, but for now the ID would be enough to get this whole thing work somehow.

Thanks in advance!

Tags:
@stefan-niedermann

[juliushaertl]

@desperateCoder @stefan-niedermann How about adding an optional If-Modified-Since header to the existing API calls (see https://tools.ietf.org/html/rfc7232#section-3.3) We could apply this to all GET requests on the API and then only return data, that has changed in newer time than the transmitted date in the header. We should then also provide proper ETags and a Last-Modified header to make sure you can properly check on that. Would that work for you?

[desperateCoder]

Hi,

sounds like this is exactly what we need!

Thanks in advance!

[stefan-niedermann]

Sounds good :) Is there any reason why you prefer a http header over an optional url parameter? (i'm just curious)

[juliushaertl]

sounds like this is exactly what we need!

Ok, great, I'll look into that.

Is there any reason why you prefer a http header over an optional url parameter? (i'm just curious)

Mostly because there is a standard, so I prefer to use it instead of adding additional parameters. :wink:

[desperateCoder]

Offtopic: Do you have any ER-Diagram or something like this? Do you think i should build the whole DB-Model into the App, or is there any irrelevant data for an App like this? Should i sync the DB-Layout or do my own thing? What is your opinion?

[juliushaertl]

@desperateCoder I don't have an ER-diagram, sorry. Yes, I think just mirroring the whole DB-Model makes sense if we want to have a feature-complete app at some point. You can have a look at the database schema here: https://gist.github.com/juliushaertl/9da2b2ba359e6ddad656d1cd17986862 Any fields with _id in name are basically foreign keys to the related tables. So if there is a stack_id field in oc_deck_cards it contains an id of an entry in oc_stacks.

You will probably need to add some additional tables. One example would be users that have access to a board. They are for example needed when assigning a card to a user. The user list is returned by the board endpoint, but not stored in our db on the server side.

I also thought a bit more about the sync mechanism. Having a unified endpoint that returns all changed elements has some problems, e.g. the app would not get any notice that a card has been deleted.

Even if a sync like this will require some more API calls, there is not to much additional logic required on the server and we stay in the REST architectural style.

For a full sync you would need to do the following then

Fetch all boards
    For all boards that have a outdated last_modified entry in the apps database
        Fetch all cards (This can then be limited with the If-Modified-Since but you would not see that cards were deleted)
            For all cards that have a outdated last_modified entry in the apps database
                Fetch attachments
        Fetch all labels
        Fetch all acl

Does that make sense?

[desperateCoder]

Hi @juliushaertl,
first of all thanks for the quick response.
OK so far, i was prefering the DB-mirroring anyway. And yes, i already have an accounts-table for saved accounts (via SSO). That is totally fine.

After you mentioned the If-Modiefied-Since header, i was already expecting something similar to your approach (more than one call for fetching changes).

Your particular idea would work just fine i guess, but only if the last_modified field in the boards-table is also updated when the user changes a stack / card, right? Does Deck work like this?

One more Question: Is Deck revision-safe? Thus, do you save a kind of history to a Card? Just wondering....

[desperateCoder]

I just looked at your gist, not sure, but don't you have constraints or indices? I'm kinda confused...

[juliushaertl]

Your particular idea would work just fine i guess, but only if the last_modified field in the boards-table is also updated when the user changes a stack / card, right? Does Deck work like this?

We don't do that right now, but I'll add that while adding the If-Modified-Since header.

One more Question: Is Deck revision-safe? Thus, do you save a kind of history to a Card? Just wondering....

No, we just keep one state in the database. However we expose changes to the Nextcloud activity app. That is good for giving the user a list of changes, but doesn't allow to properly track revisions back.

I just looked at your gist, not sure, but don't you have constraints or indices? I'm kinda confused...

We don't have foreign key contraints, since that is not supported properly by the Nextcloud app framework API. The indexes are missing because I haven't included them in my grep from the database dump. You can have a look at the full current database definition here: https://github.com/nextcloud/deck/blob/master/appinfo/database.xml The dump including the indexes is here: https://gist.github.com/juliushaertl/12c5a605aafbf6ec5a8c339e8ddd961d

[desperateCoder]

Great, that is good to know so far.
I'll start building the App-DB next.
If there are news concerning the Headers, just let me know. Also, a little more documentation of the endpoints would help a lot.

Thank you so far and sorry for spamming my questions here :)
Happy coding!