Add a shared DB transaction helper to internal/db/db.go

[Jagadeeshftw]

📌 Description

Several handlers likely hand-roll BEGIN/COMMIT/ROLLBACK transaction boilerplate around pgx calls. A small WithTx(ctx, fn) helper in internal/db/db.go would reduce duplication and the risk of a forgotten rollback-on-error path.

🧩 Requirements and context

• Add a WithTx(ctx context.Context, fn func(pgx.Tx) error) error helper that commits on nil error and rolls back otherwise.
• Ensure panics inside fn still trigger a rollback (recover + re-panic).
• Migrate at least one existing multi-statement handler to use the new helper as a proof of adoption.

🛠️ Suggested execution

• Add the helper to internal/db/db.go.
• Add a unit test covering commit, explicit error rollback, and panic-triggers-rollback.
• Refactor one handler (e.g. in internal/handlers/) that currently hand-rolls a transaction.

✅ Acceptance criteria

• WithTx helper exists with commit/rollback/panic-safety tests.
• At least one handler is migrated to use it.
• No behavior change for the migrated handler's happy path.

🔒 Security notes

Reduces the risk of a forgotten rollback leaving a transaction open/locked under error conditions, which is a minor availability concern under load.

📋 Guidelines

• Minimum 95% test coverage
• Clear documentation
• Timeframe: 96 hours

[amankoli09]

Can i work on this issue

[Jagadeeshftw]

Assigned @amankoli09 to this issue. Please open a PR referencing this issue (e.g. Closes #133) when you're ready. If you have questions along the way, feel free to join our contributor chat: https://t.me/+vTbe0hOUKrFhNTJl

[grantfox-oss]

🦊 GrantFox — @amankoli09 has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #133)
2. Your PR will be reviewed by the Grainlify maintainers

Good luck! Track your progress on GrantFox.