Cooldown on DB writes

[charred goblet]

There's this problem where im supposed to put a cooldown on write operation per user(lets call it N rows max).

1. I was thinking about what if do a query about no of rows from a particular user if it maxed out, then put a cooldown timer for some duration perhaps. This would mean before every write there's a query to DB about author xyz.

2. I can also keep track of overall writes to DB(dismiss the writes for a while if values reach a certain threshold) but this seems a bit inferior solution. This would also mean a query on but on the table (i think that's efficient in sqllite DB)

3. A separate table for each user where we keep count of all entries, then a query every x mins perhaps and if we reach a threshold. We start the cooldown but this adds more complexity in terms of handling cleanup afterwards.

Thoughts?

[sullen sky]

<@&987246924425994290> please have a look, thanks.

[sullen sky]

While you are waiting for getting help, here are some tips to improve your experience:

Code is much easier to read if posted with syntax highlighting and proper formatting.

If nobody is calling back, that usually means that your question was not well asked and hence nobody feels confident enough answering. Try to use your time to elaborate, provide details, context, more code, examples and maybe some screenshots. With enough info, someone knows the answer for sure.

Don't forget to close your thread using the command </help-thread close:1027500463647621170> when your question has been answered, thanks.

Here is an AI assisted attempt to answer your question 🤖. Maybe it helps! In any case, a human is on the way 👍. To continue talking to the AI, you can use </chatgpt:1108714622413963314>.

specific user before allowing a write operation. If the number of rows is less than N, then allow the write operation. Otherwise, reject it.

2. Another approach could be to timestamp each write operation and store it in a separate table. Before allowing a new write operation, check the timestamp of the last write operation from that user. If the time difference is less than a certain cooldown period, reject the write operation.

3. You can also implement a token-based system where each user is given a certain number of tokens for write operations within a specific time period. Each time a user performs a write operation, deduct one token from their count. If they run out of tokens, reject further write operations until their token count replenishes.

4. Additionally, you can use rate limiting techniques to control the number of writes per user per unit of time. Set a limit on the number of writes allowed within a specific timeframe (e.g., 10 writes per minute). If a user exceeds this limit, reject further write operations until the timeframe resets.

5. You can also consider implementing exponential backoff for users who exceed their allowed number of writes within a cooldown period. This means that if a user exceeds their limit, increase the cooldown duration exponentially for subsequent write operations from that user.

Remember to consider potential edge cases and handle concurrency issues when implementing any cooldown mechanism for DB writes to ensure data integrity and consistency.

[elder star]

u dont have to make it that complex actually

the thing ur talking about is a safety feature to prevent someone killing the bot, nothing more

it likely wont ever be triggered and hence also doesnt need to be that detailed

its enough if u make a simple in-memory counter (i.e. a simple int counter) which, regardless of user, u increment

or sth like that

and as soon as it goes over some high threshold, u simple skip incoming requests

nothing breaks if our meta-table doesnt contain all records

skipping wont cause trouble

dunno, just measure some sort of "requests per second" and if it goes beyond a limit, skip requests until it cooled down again

doesnt necessarily need to be per-user

u can do that easily with Caffeeine

which is essentially a Map where u can also give the records an expiration time

so on each request, u put some object in there (perhaps the request ID or sth)

and set an expiration time of perhaps 1 minute

and if the map grows beyond a certain size (perhaps 10k ?) u simply skip/ignore incoming requests

there are examples in the codebase already, just search for caffeeine

[charred goblet]

got it, i wasn't sure if table wise would be better to do. Im also not aware how requests could kill the bot. 10k seems fair.

[elder star]

i kinda lost context what it was about and for what i suggested it in which PR

[charred goblet]

a message listener was writing some meta data from each message that's being sent in chat from a member, you were concerned about if someone with malicious intent wanted they could spam the chat which could bring down the bot. There's also a routine that clears DB rows which are older than 2hrs or so.

[elder star]

ah right. tbh, i think discord auto-blocks spammers

u can skip this for now

but its always worth to have such stuff in mind when creating sth that's mass-triggered 🙂

btw, make sure to only consider messages by human

skip any bot message or these "webhook message" (for example "John pinned a message in this channel")

there should be simple isBot() and isWebhookMessage() or similiar on the message

otherwise we generate a lot of traffic considering meta channels like #logs

[charred goblet]

Btw i was wondering, how many entries would it take to do something like that? I suppose that depends on our storage?

[charred goblet]

ye i skipped both bot and webhook messages.

[elder star]

yes. i dont remember how much disk space we have, not much

ram is 1 GB though. for the entire machine

[charred goblet]

There's one more thing, purgeMessagesById returns a list of CompletableFutures. I was wondering whether i should handle failures?

[elder star]

and 1 core

[elder star]

sounds tricky to get a good ui/ux for that...

if its not too difficult, perhaps respond with how many failed

but i wouldnt do more than that

[charred goblet]

i was thinking just to log it

I was reading up on how to seperate successes and failures, perhaps i can do that for how many failed as well.

[elder star]

but without message content. otherwise u have the bad messages readable in the log channel lol

[charred goblet]

maybe just log it for anyone to see why purge-in-channel failed or something like that. We're not really worried about message content.

Idk much about Completable futures atm, but im sure i can figure something out. I was thinking something like "X messages purged, Y failed" in the logs or something along those lines. Or maybe just if there a failure.

If not to complicate things further then just logging "user abc purged messages from user xyz" is also an option.