[FEAT] Implement Real-time CDC Engine

[m-t-a97]

📋 Synopsis

Build a Real-time plugin for GoBetterAuth that uses PostgreSQL Logical Replication to stream changes. Unlike a standard listener, this engine will integrate with GORM’s Schema to automatically decode binary WAL (Write-Ahead Log) data into user-defined GORM Models/Structs before pushing them to the Event Bus.

---

🏗️ Technical Requirements

1. Dedicated Replication Connection:

• Extract the DSN from the existing GORM connection.
• Establish a separate, long-lived pgconn in replication mode (outside the GORM pool).

2. GORM Schema Mapping:

• Use db.Statement.Parse(model) to cache table-to-struct metadata.
• Map Postgres RelationID to GORM schema.Schema.

3. The Decoder (Binary -> GORM Struct):

• Implement a parser that uses GORM Field Types (OIDs) to cast values correctly.
• Support GORM-specific types: gorm.DeletedAt, UUID, and JSONB tags.

4. Automatic Identity Management:

• Provide a utility to run ALTER TABLE ... REPLICA IDENTITY FULL for tables registered for Real-time.

---

✅ Acceptance Criteria

• Model Registration: Developers can register a GORM model for real-time (e.g., auth.Realtime.Register(&User{})).
• Automatic Decoding: The Event Bus receives the actual Go Struct populated with data, not just raw strings.
• Action Parity: Correctly handles INSERT (New data), UPDATE (Diff data), and DELETE (Primary Key).
• Resilience: If the replication connection drops, it automatically resumes from the last confirmed LSN (Log Sequence Number).
• No Pool Contamination: The replication worker must not consume or block connections from the main GORM *sql.DB pool.

---

🛠️ Implementation Tasks

1. [ ] Replication Driver: Setup pglogrepl to establish the slot and publication.
2. [ ] Schema Registry: Create a thread-safe map that links Postgres RelationID to *schema.Schema.
3. [ ] GORM-Type Mapper: Write the castByOID logic specifically for GORM’s DataType (e.g., mapping time to time.Time).
4. [ ] Event Bus Publisher: Create a specialized event payload:

type RealtimeEvent struct {
    Model  string      // e.g. "User"
    Action string      // "INSERT"
    Old    interface{} // Struct before change (if Identity Full)
    New    interface{} // Struct after change
}

5. [ ] Middleware/Helper: Add auth.Realtime.Enable(db) to handle SQL-side setup.

---

📝 Notes for GORM Integration

• REPLICA IDENTITY: Remind users that for UPDATE and DELETE to show the full struct, they must enable REPLICA IDENTITY FULL on that table.
• Performance: Use a background goroutine for the replication loop so it doesn't hang the main application startup.

---

The above is subject to change.

[nutcas3]

are u planning something with Bun or any other ORM?
Can I see the roadmap maybe I can be sending PRS since its a good idea this one
cc @m-t-a97

[m-t-a97]

Hi @nutcas3 , thanks for sharing this. I'll get back to you on this as I'm currently working on a big refactor right now so I'll give you an update after I get it merged in.

But I would definitely like to switch to bun ORM from Gorm as it's faster and supports a better migration step. It's hard doing migrations in a manual way with Gorm, have to rely on golang-migrate or something else but bun ORM comes with a nice programmatic way of doing migrations which is much better IMO for GoBetterAuth.

Thanks!

[m-t-a97]

NOTE: blocked by #18

[nutcas3]

If you need aid u can shoot @m-t-a97
I may have some hours to spare on this

Since I may use it in production coming next year

[m-t-a97]

@nutcas3 that's great to hear. Looking forward to people using this in production 🙂.

Thanks and will let you know. Will need to review this ticket again anyways as it's dependent on #18 .

[m-t-a97]

Just an update on this ticket, GoBetterAuth v2.0 is out now and we've migrated to Bun ORM from GORM. So this ticket needs to be revised. I'll look into it soon and see how we can implement a plugin like this using Bun ORM now.