When metricity starts up and synchronizes the users, all users temporarily have
their in_guild field set to false.

From looking at the code I am currently unsure how this can happen. As far as I
understand, the complete upsert is run in a transaction that is committed at
the end of each individual insert.

Demonstration as taken during a metricity restart (whilst User upsert:
messages can be seen in the logs):

metricity=# SELECT in_guild, COUNT(*) FROM users GROUP BY in_guild;  -- During sync
 in_guild | count
----------+--------
 f        | 884763
(1 row)

metricity=# SELECT in_guild, COUNT(*) FROM users GROUP BY in_guild;  -- After sync
 in_guild | count
----------+--------
 f        | 494638
 t        | 390125
(2 rows)

A member update event triggered the following traceback shortly after startup:

2024-07-08 18:26:19 metricity-85d48b5747-9bpkj metricity.bot[16] ERROR Unhandled exception in on_member_update
Traceback (most recent call last):
    [ ... ]
  File "asyncpg/protocol/protocol.pyx", line 207, in bind_execute
asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1234) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
    [ ... ]
  File "/opt/poetry/cache/virtualenvs/metricity-KqPa2cfc-py3.12/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 784, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.IntegrityError: <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1234) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
    [ ... ]
  File "/metricity/metricity/exts/event_listeners/member_listeners.py", line 125, in on_member_update
    await sess.commit()
    [ ... ]
  File "/opt/poetry/cache/virtualenvs/metricity-KqPa2cfc-py3.12/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 784, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1234) already exists.
[SQL: INSERT INTO users (id, name, avatar_hash, guild_avatar_hash, joined_at, created_at, is_staff, bot, in_guild, public_flags, pending) VALUES ($1::VARCHAR, $2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::TIMESTAMP WITHOUT TIME ZONE, $6::TIMESTAMP WITHOUT TIME ZONE, $7::BOOLEAN, $8::BOOLEAN, $9::BOOLEAN, $10::JSON, $11::BOOLEAN)]
[parameters: ('1234', 'redacted', 'redacted', None, datetime.datetime(2024, 7, 8, 18, 22, 50, 0), datetime.datetime(2020, 1, 1, 1, 1, 1, 0), False, False, True, '{"staff": false, "partner": false, "hypesquad": false, "bug_hunter": false, "hypesquad_bravery": false, "hypesquad_brilliance": false, "hypesquad_bal ... (127 characters truncated) ... , "verified_bot_developer": false, "discord_certified_moderator": false, "bot_http_interactions": false, "spammer": false, "active_developer": false}', False)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Logs can currently be found via

kubectl logs -n bots metricity-85d48b5747-9bpkj --follow

This happened two seconds after the user sync was said to be complete. Of note
is that the user join timestamp is four minutes before the log in question
appeared.

Perhaps the sess.get(User, str(member.id)) call in on_member_update was
performed before the user sync inserted it, then the coroutine didn't get
scheduled back in time,

I think the proper fix here might be to switch to SERIALIZABLE transaction
level. See
https://blog.ydb.tech/do-we-fear-the-serializable-isolation-level-more-than-we-fear-subtle-bugs-5a025401b609
for some more information.

When starting metricity, the following deprecation warning (?) is printed to the log:

Warning: 'start' is an entry point defined in pyproject.toml, but it's not installed as a script. You may
get improper `sys.argv[0]`.

The support to run uninstalled scripts will be removed in a future release.

Run `poetry install` to resolve and get rid of this message.

We need to check whether we have to action this.

Automod messages are sent with the original user as the author, which means they are recorded as that user's messages in metricity. Messages with the automod type should be ignored.

Currently the thread archive state is only synchronized on startup. This means
that the archived flag on threads in the database slowly runs out of sync.

We should probably listen for
on_thread_update
or
on_raw_thread_update
here and update the database accordingly.

Changes included in this PR

I recommend upgrading to python:3.11.0a7-slim, as this image has only 44 known vulnerabilities.

Some of the most important vulnerabilities in your base image include:

Severity	Priority Score / 1000	Issue
	714	Buffer Overflow SNYK-DEBIAN10-OPENSSL-1569403
	614	Out-of-bounds Read SNYK-DEBIAN10-OPENSSL-1569406
	514	CVE-2021-4160 SNYK-DEBIAN10-OPENSSL-2388381
	678	Loop with Unreachable Exit Condition ('Infinite Loop') SNYK-DEBIAN10-OPENSSL-2426310
	678	Loop with Unreachable Exit Condition ('Infinite Loop') SNYK-DEBIAN10-OPENSSL-2426310

By

Snyk

Running locally on Windows I get this error.

metricity.database[24544] INFO Initiating connection to the database
Ignoring exception in on_guild_available
Traceback (most recent call last):
  File "C:\Users\wookie184\AppData\Local\pypoetry\Cache\virtualenvs\metricity-iF629sR3-py3.9\lib\site-packages\discord\client.py", line 373, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\wookie184\Documents\GitHub\metricity\metricity\bot.py", line 218, in on_guild_available
    await db_ready.wait()
  File "c:\users\wookie184\appdata\local\programs\python\python39\lib\asyncio\locks.py", line 226, in wait
    await fut
RuntimeError: Task <Task pending name='discord.py: on_guild_available' coro=<Client._run_event() running at C:\Users\wookie184\AppData\Local\pypoetry\Cache\virtualenvs\metricity-iF629sR3-py3.9\lib\site-packages\discord\client.py:373>> got Future <Future pending> attached to a different loop

I don't really know the meaning of that beyond what it says, although changing the start function in __main__.py to this seemed to fix it:

def start() -> None:
    """Start the Metricity application."""
    loop = asyncio.get_event_loop()
    loop.run_until_complete(bot.start(BotConfig.token))

I'm take a guess it's something to do with

sync_process_complete = asyncio.Event()
channel_sync_in_progress = asyncio.Event()
db_ready = asyncio.Event()

in bot.py creating an event loop and then bot.run creating a new event loop, but i'm really not sure. Also not sure why it's a windows only thing (guessing it is, since it works on the server).

At the moment no messages are inserted for the time when metricity is not available, rendering statistics during that time empty.

It would be nice if metricity could backfill this data on startup. For example, we could check something like SELECT channel_id, max(id::int) FROM messages GROUP BY channel_id, iterate over the history of each channel using after=message_id, and then insert any messages. From my understanding duplicate inserts should mostly be a no-op (possibly needing a ON CONFLICT DO IGNORE).

Hello! It's me!

I had a quick chat with vco on the DMD last night, and this issue was suggested!

Basically, we're using Metricity with Metabase at QuiltMC, but it doesn't have any support for threads. Since we use threads extensively, it'd be outstanding if Metricity was able to track threads in the following capacity:

• Whether a thread is archived or not
• Whether a thread is locked or not
• When the thread was created
• The name of the thread
• What channel a thread belongs to
• What thread a message was sent in (generally it may be best to store both the thread ID and parent channel ID for filtering)

I hear that PyDis may eventually embrace threads, so this may be useful for you in the long run as well.

As the title specifies, the database should be updated when a user edits their message to ensure accuracy of content.