Bot-Side Report Handling Compatibility Assessment (Batch 2)

1. Bot Report Format Details

Location: crates/cgcx-bot/src/main.rs:1720-1730

The bot constructs the forwarded report message as follows (HTML parse mode):

<b>[ NEW REPORT ]</b> #{report_id}

CXID: <code>{cxid}</code>
Reporter: <code>{reporter_id}</code>
Owner: <code>{content.user_id}</code>
Uploaded: <i>{YYYY-MM-DD HH:MM}</i>
Files: <b>1</b>

Notes:

report_id is the auto-incremented SQLite row ID returned by ReportRepo::insert.
cxid is extracted from the user’s message via extract_cxid(text).
reporter_id is the Telegram user_id of the person reporting.
content.user_id is the owner/uploader of the reported content.
Files: <b>1</b> is hardcoded to 1 regardless of actual file count.

2. Inline Keyboard Layout Details

Location: crates/cgcx-bot/src/main.rs:1732-1742

The inline keyboard is a 2×2 grid:

Row	Button Label	Callback Data
1	`[ Rmv + Ban ]`	`v1:admin:delblk:{report_id}`
1	`[ Delete Only ]`	`v1:admin:del:{report_id}`
2	`[ Blacklist Only ]`	`v1:admin:blk:{report_id}`
2	`[ Ignore ]`	`v1:admin:ign:{report_id}`

These callbacks are handled by handle_admin_callback (main.rs:1745), which:

Validates the user is an admin in the review group chat.
Looks up the Report by report_id.
Performs the requested moderation action and resolves the report.

3. `ReportRepo::insert` Signature & Behavior

Location: crates/cgcx-db/src/repos.rs:426-433

pub async fn insert(
    &self,
    content_id: &ContentId,
    reporter_user_id: i64,
    reason: &str,
) -> Result<i64> {
    let conn = self.conn.lock().await;
    conn.execute(
        "INSERT INTO reports (content_id, reporter_user_id, reason) VALUES (?1, ?2, ?3)",
        params![content_id.as_str(), reporter_user_id, reason],
    )?;
    Ok(conn.last_insert_rowid())
}

Returns last_insert_rowid() (the generated report_id).
No additional validation inside the repo; the caller is responsible for ensuring content_id exists.

4. Compatibility Assessment for Web Reports (`reporter_user_id = 0`)

❌ Critical Issue: Foreign Key Constraint Violation

Schema: migrations/001_init.sql:39-50

CREATE TABLE reports (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id      TEXT NOT NULL REFERENCES contents(id),
    reporter_user_id INTEGER NOT NULL REFERENCES users(id),
    reason          TEXT NOT NULL,
    ...
);

Foreign key enforcement is enabled:

cgcx-db/src/lib.rs:21 and :33 both execute PRAGMA foreign_keys = ON;.

Impact:

Passing reporter_user_id = 0 to ReportRepo::insert will fail with a foreign key constraint violation because there is no user row with id = 0.
There is no anonymous/web user seed or special-case handling anywhere in the codebase.

⚠️ Secondary Issue: Reason Field Semantics

Location: crates/cgcx-bot/src/main.rs:1719

let report_id = report_repo.insert(&content_id, reporter_id, text).await?;

In the bot flow, text is the raw user message (a cxid or share link). The bot stores this raw cxid/link as the reason.
A web report API would naturally accept separate cxid and reason fields. If the server replicates the bot behavior by passing the cxid as the reason, the database will contain a machine ID instead of a human-readable report reason.
Recommendation: The server should pass the user-supplied human reason (or a placeholder like "Web report") to ReportRepo::insert, not the cxid.

⚠️ Tertiary Issue: Reporter Display

The report message displays Reporter: <code>{reporter_id}</code>. If reporter_id = 0, moderators will see Reporter: <code>0</code>, which is indistinguishable from a real user and not user-friendly.
Recommendation: Consider creating a dedicated anonymous/web reporter user (e.g., id = 0 or a negative sentinel) with a recognizable username, or adjust the report template to show "Web" / "Anonymous" when the reporter is not a Telegram user.

5. Recommendations

Create an anonymous/web reporter user row (e.g., id = 0 or a dedicated negative ID) in the users table before any web report can be inserted, OR relax the NOT NULL / foreign-key constraint on reporter_user_id (requires migration).
Update the server-side report endpoint to accept a separate reason field and pass it to ReportRepo::insert, rather than mirroring the bot’s cxid-as-reason behavior.
Align the report message template for web reports so that the Reporter: line is meaningful (e.g., "Reporter: Web" or "Reporter: Anonymous") instead of a raw numeric 0.
Optional: Fix the hardcoded Files: <b>1</b> in the bot template to use the actual file count from ContentFileRepo::list_by_content, so the server report and bot report are consistent.

6. Summary Table

Aspect	Bot Behavior	Web Report Compatibility	Risk
Message format	HTML with hardcoded `Files: 1`	Server can replicate easily	Low (cosmetic)
Keyboard layout	2×2 grid with `v1:admin:*:{id}`	Fully compatible	None
`ReportRepo::insert`	Accepts any `i64` for `reporter_user_id`	Fails at runtime for `0`	High
`reason` field	Stores raw cxid/link	Misleading if replicated verbatim	Medium
Reporter display	Raw numeric ID	`0` is uninformative	Low

5.5 KiB Raw Permalink Blame History Unescape Escape