# Nextcloud MariaDB Direct Queries

## Connection

Container: `nextcloud-db-1`
Database: `nextcloud`
CLI: `mariadb` (NOT `mysql`)
Credentials from `~/nextcloud/docker-compose.yml`:

```bash
docker exec nextcloud-db-1 mariadb -u nextcloud -p'Nc2026Db!Szw' nextcloud -e "<SQL>"
```

## File Metadata (oc_filecache)

When was a file stored? Who owns it? What's its real size?

```sql
SELECT fileid, path,
       FROM_UNIXTIME(storage_mtime) as stored_utc,
       FROM_UNIXTIME(mtime) as modified_utc,
       size
FROM oc_filecache
WHERE path LIKE '%关键词%';
```

Key fields:
- `storage_mtime`: when the file was written to disk (UNIX timestamp)
- `mtime`: last modification time
- These are UTC — add 8 hours for Beijing time

## Activity Log (oc_activity)

Who created/uploaded/modified a file?

```sql
SELECT activity_id,
       FROM_UNIXTIME(timestamp) as time_utc,
       user, subject, file
FROM oc_activity
WHERE file LIKE '%关键词%'
ORDER BY timestamp ASC;
```

Key `subject` values:
- `created_self` — user uploaded/created the file
- `changed_self` — user modified the file
- `deleted_self` — user deleted the file

## Important Distinction

Files uploaded via **docker cp + occ files:scan** (our automated workflow) do NOT create an `oc_activity` record. Only files uploaded through the Nextcloud web UI or sync client do. So:

- Activity record exists → uploaded by that user through Nextcloud UI
- No activity record but file exists in `oc_filecache` → uploaded via docker cp (automated pipeline)

## Provenance Investigation Checklist

When Doro asks "这份文件哪来的" or "存入时间是什么时候":

1. **cache/documents/** — check `stat` for when WeChat received it (Birth time)
2. **oc_filecache** — `storage_mtime` for when it landed on Nextcloud disk
3. **oc_activity** — check if there's a `created_self` record (means web upload)
4. **No activity record** → file was put there by automated workflow (docker cp)
5. **gateway journal** — `journalctl --user -u hermes-gateway | grep "文件关键词"` for WeChat reception time
6. **session_search** — search for the filename to find which session processed it

All timestamps are UTC on server. Always convert to Beijing time (UTC+8) before reporting to Doro.