Track Discord gigs in Postgres and promote signed gigs into projects with CRM/Kimai links

[michaelmwu]

Problem

We currently post gigs directly in Discord, but we do not persist them as first-class records in the app. That means we cannot reliably track:

• pending gigs before contract signature
• who has applied / been suggested for a gig
• who is actually staffed on the work
• when a gig graduates into a signed project
• links back to CRM people and ERP/Kimai projects

We already have useful ingredients in the repo:

• Discord-side gig/job ingestion and matching flows
• a Postgres-backed local people cache synced from CRM
• Kimai integrations for project visibility

The missing piece is a local system of record for the gig/project lifecycle.

Recommendation

Use Postgres as the system of record for this workflow.

• Discord is the ingestion/source of new gigs
• CRM remains the external source for people/accounts metadata
• Kimai/ERP remains the external source for signed-project / paid-work context
• Postgres owns lifecycle state, joins, history, and automation

Do not make CRM the only source of truth for gigs. Pending gigs are born in Discord, and signed projects may later gain ERP/Kimai linkage. This needs one local record that can survive that transition.

Lifecycle Model

Treat this as one entity with two lifecycle stages, not two unrelated records:

• pending_gig: posted in Discord, not yet contracted
• project: contract signed / work is real / may be linked to Kimai

A pending gig should be able to graduate in-place to a project so we do not lose history, applicants, or Discord provenance.

Proposed Data Model

engagements

One row per gig/project across its lifecycle.

Suggested fields:

• id
• lifecycle_stage (pending_gig, project)
• status (open, sourcing, shortlisting, staffed, in_progress, completed, canceled, archived)
• title
• body_raw
• body_normalized
• openings
• required_skills
• preferred_skills
• location_text
• client_name
• posted_at
• contract_signed_at nullable
• created_at
• updated_at

Discord provenance:

• discord_guild_id
• discord_channel_id
• discord_message_id unique
• discord_thread_id nullable
• posted_by_discord_user_id
• edited_at nullable
• deleted_at nullable

External links:

• crm_account_id nullable
• kimai_project_id nullable
• kimai_customer_id nullable

engagement_applications

Tracks people considered for a pending gig/project.

Suggested fields:

• id
• engagement_id
• person_id
• status (suggested, reviewing, contacted, interviewing, offered, accepted, rejected, withdrawn)
• source (auto_match, manual_add, discord, crm, erp)
• match_score nullable
• notes nullable
• created_at
• updated_at

Unique key:

• (engagement_id, person_id)

engagement_people

Tracks people actually attached to the work, including staffed members and internal owners.

Suggested fields:

• id
• engagement_id
• person_id
• role (owner, recruiter, account_manager, candidate, assigned_member, client_contact)
• status (active, inactive, completed, removed)
• source (manual, discord, crm, erp)
• created_at
• updated_at

This table should be able to mirror paid/staffed people linked from ERP/Kimai once a gig becomes a project.

engagement_events

Append-only audit/history table.

Suggested fields:

• id
• engagement_id
• event_type
• actor_person_id nullable
• payload jsonb
• created_at

Examples:

• engagement_created_from_discord
• engagement_reparsed
• application_added
• application_status_changed
• person_assigned
• promoted_to_project
• kimai_linked
• crm_linked

Automation Behavior

New Discord gigs

• New message in the gigs channel creates an engagements row with lifecycle_stage = pending_gig
• Message edits update the stored body snapshot and trigger re-parse
• Message deletion should archive/cancel the engagement instead of hard-deleting it

Applicants / suggested people

• Reuse the existing candidate search against the local people cache
• Store suggested candidates in engagement_applications
• Allow manual status progression over time

Promotion to project

• When contract is signed, update the same row:
  • lifecycle_stage = project
  • set contract_signed_at
  • attach kimai_project_id / kimai_customer_id when available
• Preserve Discord origin, applications, and prior event history

ERP/Kimai / CRM linking

• CRM linkage should attach known people/accounts to the engagement
• Kimai linkage should attach the signed project record and, when available, people who are actually getting paid / logging time
• Do not require all systems to be populated on day one; support partial linkage

Backfill Strategy

We do not need to make everything manual. Recommended staged backfill:

1. Discord backfill for pending gigs

   • One-time script to read recent history from the gigs channel and create pending_gig engagements
   • Manual review only for posts that cannot be parsed confidently

2. Kimai/ERP backfill for signed projects

   • Import existing Kimai projects into engagements as project rows when they can be matched
   • Pull linked/paid people where available

3. CRM linkage backfill

   • Attach CRM account/person references where we can confidently match existing local people rows / contacts
   • Leave unresolved links nullable for manual cleanup

4. Review queue for low-confidence matches

   • When Discord/CRM/Kimai records cannot be confidently merged, create a manual review path instead of guessing

Implementation Notes

• Keep Postgres migrations in apps/worker/src/five08/worker/migrations
• Reuse the existing worker job model for ingestion, reparsing, sync, and reconciliation tasks
• Reuse the local people table as the canonical person dimension inside the app
• Start with Kimai-specific linkage if that is the ERP currently in use; generalize later only if needed

Acceptance Criteria

• New gigs posted in Discord are persisted as pending_gig engagements
• Engagement records keep stable Discord provenance (message_id, channel, author, timestamps)
• Suggested/applicant people can be attached and statused over time
• A pending gig can be promoted in-place to a project without losing history
• Signed projects can link to Kimai project/customer records
• People attached via CRM/local people cache and via Kimai can both be represented on the same engagement
• One-time backfill exists for Discord gigs and Kimai projects, with manual review for ambiguous matches

[michaelmwu]

Issue breakdown for implementation:

• Track pending Discord gigs, applicants, and lifecycle commands with post tag updates #252 Track pending Discord gigs, applicants, and lifecycle commands with post tag updates
• Promote pending gigs into projects and link ERPNext/CRM records #251 Promote pending gigs into projects and link ERPNext/CRM records
• Backfill Discord gigs and reconcile ERPNext/CRM links into local engagement records #250 Backfill Discord gigs and reconcile ERPNext/CRM links into local engagement records

[michaelmwu]

Additional child issue:

• Detect stale gigs and send Discord reminders when status has not been updated #253 Detect stale gigs and send Discord reminders when status has not been updated