πŸ”₯

CultivaLeads β€” Firebase SQL Connect Backend

DiseΓ±o completo: schema GraphQL Β· operaciones autorizadas Β· SDK TypeScript para Next.js 14
πŸ”₯ Firebase SQL Connect 🐘 Cloud SQL PostgreSQL β—ˆ GraphQL Schema TS TypeScript SDK β–² Next.js 14 App Router
⚑

Flujo de Desarrollo

1
Inicializar
firebase init dataconnect
2
Schema GQL
Definir tipos con @table y relaciones
3
Operaciones
Queries + Mutations con @auth/@check
4
Generar SDK
dataconnect:sdk:generate β†’ TypeScript tipado
5
Deploy
firebase deploy --only dataconnect
$ npx -y firebase-tools@latest init dataconnect βœ“ Created dataconnect/dataconnect.yaml
$ npx -y firebase-tools@latest emulators:start --only dataconnect βœ“ SQL Connect emulator running on http://127.0.0.1:9399
$ npx -y firebase-tools@latest dataconnect:sdk:generate βœ“ Generated TypeScript SDK β†’ src/lib/dataconnect/
$ npx -y firebase-tools@latest deploy --only dataconnect βœ“ Deployed to Cloud SQL (us-central1) β€” cultivaleads-prod
1

Modelo de Datos β€” Entidades y Relaciones

πŸ“ Entidades del Dominio CultivaLeads schema.gql
Agency @table
idUUIDPK
nameString!
slugString!UNIQUE
planAgencyPlan!
seatsInt!
createdAtTimestamp!AUTO
AgencyMember @table
userUser!PK
agencyAgency!FK
roleMemberRole!IDX
joinedAtTimestamp!AUTO
Campaign @table
idUUIDPK
agencyAgency!FK
nameString!
channelCampaignChannel!IDX
budgetFloat
statusCampaignStatus!IDX
Lead @table
idUUIDPK
agencyAgency!FK
campaignCampaignFK
assignedToUserFK
statusLeadStatus!IDX
scoreInt!CF
emailString!UNIQUE
LeadActivity @table
idUUIDPK
leadLead!FK
authorUser!FK
typeActivityType!IDX
noteString
createdAtTimestamp!AUTO
Tag @table
idUUIDPK
agencyAgency!FK
nameString!
colorString
PKPrimary Key FKForeign Key (@ref) IDX@index/@unique AUTO@default(expr) CFCloud Function computed
πŸ“„ schema.gql β€” Tipos y Enums schema.gql
# dataconnect/schema/schema.gql # CultivaLeads β€” Modelo relacional type User @table(key: "uid") { uid: String! @default(expr: "auth.uid") email: String! @unique displayName: String avatarUrl: String createdAt: Timestamp! @default(expr: "request.time") } enum AgencyPlan { STARTER GROWTH ENTERPRISE } enum MemberRole { OWNER MANAGER SDR } enum CampaignChannel { META GOOGLE LINKEDIN EMAIL ORGANIC } enum CampaignStatus { DRAFT ACTIVE PAUSED COMPLETED } enum LeadStatus { NEW CONTACTED QUALIFIED PROPOSAL WON LOST } enum ActivityType { CALL EMAIL MEETING NOTE DEMO } type Agency @table { id: UUID! @default(expr: "uuidV4()") name: String! slug: String! @unique plan: AgencyPlan! @default(value: STARTER) seats: Int! @default(value: 5) createdAt: Timestamp! @default(expr: "request.time") } type AgencyMember @table(key: ["user"]) { user: User! agency: Agency! role: MemberRole! @default(value: SDR) joinedAt: Timestamp! @default(expr: "request.time") } type Campaign @table { id: UUID! @default(expr: "uuidV4()") agency: Agency! name: String! channel: CampaignChannel! @index budget: Float status: CampaignStatus! @default(value: DRAFT) @index createdAt: Timestamp! @default(expr: "request.time") } type Lead @table { id: UUID! @default(expr: "uuidV4()") agency: Agency! campaign: Campaign assignedTo: User email: String! @unique firstName: String! lastName: String company: String @index status: LeadStatus! @default(value: NEW) @index score: Int! @default(value: 0) phone: String linkedinUrl: String notes: String createdAt: Timestamp! @default(expr: "request.time") updatedAt: Timestamp! @default(expr: "request.time") } type LeadActivity @table { id: UUID! @default(expr: "uuidV4()") lead: Lead! author: User! type: ActivityType! @index note: String outcome: String createdAt: Timestamp! @default(expr: "request.time") } type Tag @table { id: UUID! @default(expr: "uuidV4()") agency: Agency! name: String! color: String @default(value: "#6366f1") } # Join table Tag ↔ Lead (many-to-many) type LeadTag @table(key: ["lead", "tag"]) { lead: Lead! tag: Tag! }
πŸ”— Relaciones entre Entidades @ref auto-generated
Agency β†’ 1:N AgencyMember members_on_agency
Agency β†’ 1:N Campaign campaigns_on_agency
Agency β†’ 1:N Lead leads_on_agency
Campaign β†’ 1:N Lead leads_on_campaign
User (SDR) β†’ 1:N Lead (assigned) leads_on_assignedTo
Lead β†’ 1:N LeadActivity leadActivities_on_lead
Lead ↔ N:M via LeadTag Tag tags_via_LeadTag
πŸ”„ Pipeline de Leads (enum LeadStatus)
NEW
Entrada
β†’
CONTACTED
1er contacto
β†’
QUALIFIED
BANT OK
β†’
PROPOSAL
Propuesta
β†’
WON
Cierre +
β†˜
LOST
Descartado
2

Operaciones Autorizadas β€” Queries

πŸ” queries.gql β€” Consultas de Leads @auth(level: USER)
# MANAGER: listar leads de su agencia con filtros query ListAgencyLeads( $status: LeadStatus, $campaignId: UUID, $search: String, $limit: Int, $offset: Int ) @auth(level: USER) { leads( where: { agency: { members_on_agency: { user: { uid: { eq_expr: "auth.uid" }}, role: { in: [OWNER, MANAGER] } } }, status: { eq: $status }, campaign: { id: { eq: $campaignId } }, _or: [ { firstName: { contains: $search } }, { lastName: { contains: $search } }, { company: { contains: $search } } ] }, orderBy: [{ score: DESC }, { updatedAt: DESC }], limit: $limit, offset: $offset ) { id email firstName lastName company status score phone linkedinUrl campaign { id name channel } assignedTo { uid displayName avatarUrl } tags: tags_via_LeadTag { id name color } activities: leadActivities_on_lead( orderBy: [{ createdAt: DESC }], limit: 3 ) { type note createdAt author { displayName } } updatedAt } } # SDR: solo sus leads asignados query MyLeads( $status: LeadStatus ) @auth(level: USER) { leads( where: { assignedTo: { uid: { eq_expr: "auth.uid" } }, status: { eq: $status } }, orderBy: [{ score: DESC }] ) { id email firstName lastName company status score phone campaign { name channel } tags: tags_via_LeadTag { name color } } }
πŸ“Š queries.gql β€” Dashboard y CampaΓ±as @auth + @refresh
# Dashboard KPIs β€” se refresca al actualizar lead query AgencyDashboard @auth(level: USER) @refresh(onMutationExecuted: { operation: "UpdateLeadStatus" }) { # Leads por estado (agrupar en cliente) newLeads: leads(where: { agency: { members_on_agency: { user: { uid: { eq_expr: "auth.uid" }} }}, status: { eq: NEW } }) { id } wonLeads: leads(where: { agency: { members_on_agency: { user: { uid: { eq_expr: "auth.uid" }} }}, status: { eq: WON } }) { id } activeCampaigns: campaigns(where: { agency: { members_on_agency: { user: { uid: { eq_expr: "auth.uid" }} }}, status: { eq: ACTIVE } }) { id name channel budget } } # Leaderboard SDRs β€” live cada 60s query SdrLeaderboard @auth(level: USER) @refresh(every: { seconds: 60 }) { agencyMembers( where: { role: { eq: SDR }}, orderBy: [{ joinedAt: ASC }] ) { user { uid displayName avatarUrl wonLeads: leads_on_assignedTo( where: { status: { eq: WON } } ) { id } } role joinedAt } } # Perfil del usuario actual (upsert on login) query GetMyProfile @auth(level: USER) { agencyMember(key: { user: { uid_expr: "auth.uid" } }) { role joinedAt user { uid email displayName avatarUrl } agency { id name slug plan seats } } }
3

Mutaciones Transaccionales

✏️ mutations.gql β€” Leads y Actividades @transaction
# Upsert perfil al hacer login (CRITICAL) mutation UpsertMyProfile( $email: String!, $displayName: String, $avatarUrl: String ) @auth(level: USER) { user_upsert(data: { uid_expr: "auth.uid", email: $email, displayName: $displayName, avatarUrl: $avatarUrl }) } # SDR: crear lead en su agencia mutation CreateLead( $agencyId: UUID!, $email: String!, $firstName: String!, $lastName: String, $company: String, $campaignId: UUID ) @auth(level: USER) @transaction { # Verificar que el user pertenece a la agencia query @redact { agencyMember(key: { user: { uid_expr: "auth.uid" } }) @check(expr: "this != null", message: "Not a member") { agency { id @check(expr: "this == request.variables.agencyId", message: "Agency mismatch") } } } lead_insert(data: { agency: { id: $agencyId }, email: $email, firstName: $firstName, lastName: $lastName, company: $company, campaign: { id: $campaignId }, assignedTo: { uid_expr: "auth.uid" } }) } # Mover lead en el pipeline + registrar actividad mutation UpdateLeadStatus( $leadId: UUID!, $status: LeadStatus!, $activityNote: String ) @auth(level: USER) @transaction { # Solo el SDR asignado o MANAGER/OWNER query @redact { lead(id: $leadId) @check(expr: "this != null", message: "Lead not found") { assignedTo { uid @check( expr: "this == auth.uid || auth.token.role in ['MANAGER','OWNER']", message: "Access denied" ) } } } lead_update(id: $leadId, data: { status: $status, updatedAt_expr: "request.time" }) leadActivity_insert(data: { lead: { id: $leadId }, author: { uid_expr: "auth.uid" }, type: NOTE, note: $activityNote }) } # AΓ±adir actividad (trigger Cloud Function) mutation LogActivity( $leadId: UUID!, $type: ActivityType!, $note: String, $outcome: String ) @auth(level: USER) { leadActivity_insert(data: { lead: { id: $leadId }, author: { uid_expr: "auth.uid" }, type: $type, note: $note, outcome: $outcome }) }
βš™οΈ mutations.gql β€” Admin y Tags OWNER only
# OWNER: crear agencia + asignarse OWNER mutation CreateAgency( $name: String!, $slug: String! ) @auth(level: USER) @transaction { agency_insert(data: { name: $name, slug: $slug }) agencyMember_insert(data: { user: { uid_expr: "auth.uid" }, agency: { id_expr: "response.agency_insert.id" }, role: OWNER }) } # OWNER: invitar miembro mutation InviteMember( $agencyId: UUID!, $userUid: String!, $role: MemberRole! ) @auth(level: USER) @transaction { # Solo OWNER puede invitar query @redact { agencyMember(key: { user: { uid_expr: "auth.uid" } }) { role @check( expr: "this == 'OWNER'", message: "Only OWNER can invite" ) } } agencyMember_upsert(data: { user: { uid: $userUid }, agency: { id: $agencyId }, role: $role }) } # Asignar lead a SDR (MANAGER+) mutation AssignLead( $leadId: UUID!, $sdrUid: String! ) @auth(level: USER) @transaction { query @redact { agencyMember(key: { user: { uid_expr: "auth.uid" } }) { role @check( expr: "this in ['OWNER','MANAGER']", message: "Managers only" ) } } lead_update(id: $leadId, data: { assignedTo: { uid: $sdrUid } }) } # Tags β€” crear y etiquetar lead mutation CreateTag( $agencyId: UUID!, $name: String!, $color: String ) @auth(level: USER) { tag_insert(data: { agency: { id: $agencyId }, name: $name, color: $color }) } mutation TagLead( $leadId: UUID!, $tagId: UUID! ) @auth(level: USER) { leadTag_upsert(data: { lead: { id: $leadId }, tag: { id: $tagId } }) } mutation UntagLead( $leadId: UUID!, $tagId: UUID! ) @auth(level: USER) { leadTag_delete(key: { lead: { id: $leadId }, tag: { id: $tagId } }) }
4

Matriz de Seguridad por Rol

πŸ”’ AutorizaciΓ³n por operaciΓ³n y rol @auth Β· @check Β· @redact
OperaciΓ³n @auth level OWNER MANAGER SDR Mecanismo
ListAgencyLeads USER βœ“ βœ“ βœ— where.agency.members_on_agency.role.in
MyLeads USER βœ“ βœ“ βœ“ where.assignedTo.uid.eq_expr
CreateLead USER βœ“ βœ“ βœ“ @transaction @check agency membership
UpdateLeadStatus USER βœ“ βœ“ Solo asignado @check this==auth.uid || role in [...]
AssignLead USER βœ“ βœ“ βœ— @check role in ['OWNER','MANAGER']
InviteMember USER βœ“ βœ— βœ— @check role == 'OWNER'
LogActivity USER βœ“ βœ“ βœ“ auth.uid auto-injected
AgencyDashboard USER βœ“ βœ“ βœ“ @refresh onMutationExecuted
5

ConfiguraciΓ³n y SDK TypeScript

πŸ“ Estructura del Proyecto connector.yaml
cultivaleads-next/ β”œβ”€β”€ dataconnect/ # Firebase SQL Connect β”‚ β”œβ”€β”€ dataconnect.yaml # config servicio β”‚ β”œβ”€β”€ seed_data.gql # datos de prueba (local) β”‚ └── connector/ β”‚ β”œβ”€β”€ connector.yaml # SDK gen config β”‚ β”œβ”€β”€ schema.gql # ← Paso 1 β”‚ β”œβ”€β”€ queries.gql # ← Paso 2a β”‚ └── mutations.gql # ← Paso 2b β”œβ”€β”€ src/ β”‚ β”œβ”€β”€ lib/dataconnect/ # auto-generado β”‚ β”‚ β”œβ”€β”€ index.ts # entry point SDK β”‚ β”‚ β”œβ”€β”€ schema.d.ts # tipos GQL β†’ TS β”‚ β”‚ └── esm/ # ESM bundle β”‚ └── app/ β”‚ β”œβ”€β”€ (dashboard)/ β”‚ β”‚ β”œβ”€β”€ layout.tsx β”‚ β”‚ └── leads/ β”‚ β”‚ └── page.tsx # usa SDK β”‚ └── api/score-lead/ β”‚ └── route.ts # Cloud Function hook └── firebase.json
connector.yaml SDK gen
connectorId: cultivaleads-connector authMode: Firebase generate: javascriptSdk: outputDir: "../src/lib/dataconnect" package: "@cultivaleads/dataconnect" packageJsonDir: "../src/lib/dataconnect"
βš›οΈ SDK Usage β€” Next.js 14 App Router TypeScript tipado
leads/page.tsx
firebase.ts
use-leads.ts
import { getDataConnect } from 'firebase/data-connect'; import { connectorConfig } from '@cultivaleads/dataconnect'; import { listAgencyLeadsRef, myLeadsRef, createLeadMutationRef, updateLeadStatusMutationRef, logActivityMutationRef, subscribe } from '@cultivaleads/dataconnect'; import { LeadStatus, ActivityType } from '@cultivaleads/dataconnect'; // Inicializar conector (una vez en lib/firebase.ts) const dc = getDataConnect(connectorConfig); // Hook: cargar leads del manager con suscripciΓ³n live export function useAgencyLeads(filters: { status?: LeadStatus; search?: string; }) { const [leads, setLeads] = useState<Lead[]>([]); const [loading, setLoading] = useState(true); useEffect(() => { // subscribe() reacciona al @refresh onMutationExecuted const unsub = subscribe( listAgencyLeadsRef(dc, { status: filters.status, search: filters.search, limit: 50 }), { onNext: (result) => { setLeads(result.data.leads); setLoading(false); }, onError: (err) => console.error(err) } ); return () => unsub(); }, [filters.status, filters.search]); return { leads, loading }; } // Mover lead en pipeline async function moveLead( leadId: string, status: LeadStatus, note: string ) { // Tipos inferidos 100% β€” autocompletado en IDE await updateLeadStatusMutationRef(dc, { leadId, // UUID β€” typo check status, // LeadStatus enum activityNote: note }).execute(); } // Registrar actividad β†’ dispara Cloud Function (score) async function logActivity( leadId: string, type: ActivityType, note: string ) { await logActivityMutationRef(dc, { leadId, type, note }).execute(); // ↑ onMutationExecuted β†’ CF recalcula score del lead }
6

Cloud Function β€” Score Calculator

⚑ functions/src/scoreLead.ts β€” Trigger on LogActivity mutation onMutationExecuted
import { onMutationExecuted } from 'firebase-functions/v2/dataconnect'; import { getDataConnect } from 'firebase-admin/data-connect'; import { updateLeadScoreMutationRef, ActivityType } from '@cultivaleads/dataconnect-admin'; // PuntuaciΓ³n por tipo de actividad const ACTIVITY_SCORE: Record<ActivityType, number> = { DEMO: 20, MEETING: 15, CALL: 10, EMAIL: 5, NOTE: 2 }; export const scoreLeadOnActivity = onMutationExecuted( { operation: 'LogActivity', connector: 'cultivaleads-connector' }, async (event) => { const { leadId, type } = event.data.variables; const scoreIncrease = ACTIVITY_SCORE[type as ActivityType] ?? 0; if (scoreIncrease === 0) return; const dc = getDataConnect(); await updateLeadScoreMutationRef(dc, { leadId, // Incremento relativo con Native SQL }).execute(); } );
🌱 seed_data.gql β€” Datos de prueba locales LOCAL ONLY
# dataconnect/seed_data.gql # Solo para el emulador local mutation SeedCultivaLeads @auth(level: NO_ACCESS) { # Agencia demo agency_insert(data: { id: "aaaa-1111-bbbb-2222", name: "Cultiva Digital SL", slug: "cultiva-digital", plan: GROWTH, seats: 10 }) # CampaΓ±a META activa campaign_insert(data: { id: "cccc-3333-dddd-4444", agency: { id: "aaaa-1111-bbbb-2222" }, name: "Q3 Lead Gen β€” META", channel: META, budget: 3500.00, status: ACTIVE }) # Leads de muestra leads_insertMany(data: [ { email: "sofia.garcia@techmadrid.es", firstName: "SofΓ­a", lastName: "GarcΓ­a", company: "TechMadrid SL", status: QUALIFIED, score: 65, agency: { id: "aaaa-1111-bbbb-2222" }, campaign: { id: "cccc-3333-dddd-4444" } }, { email: "carlos.ruiz@growthlab.io", firstName: "Carlos", lastName: "Ruiz", company: "GrowthLab", status: PROPOSAL, score: 82, agency: { id: "aaaa-1111-bbbb-2222" }, campaign: { id: "cccc-3333-dddd-4444" } }, { email: "ana.lopez@saasbarcelona.com", firstName: "Ana", lastName: "LΓ³pez", company: "SaaS Barcelona", status: WON, score: 95, agency: { id: "aaaa-1111-bbbb-2222" } } ]) }
πŸ“Š Resumen del Backend Generado Metrics
8
Tipos GQL (@table)
5
Enums definidos
4
Queries (@refresh)
9
Mutations (5 @transaction)
CaracterΓ­sticas clave
βœ“ Row-level security con @check + @redact
βœ“ Transacciones atΓ³micas con @transaction
βœ“ Live queries con @refresh(onMutationExecuted)
βœ“ SDK TypeScript auto-generado con tipos 100% inferidos
βœ“ Cloud Function trigger para cΓ‘lculo de score
βœ“ M:N leads↔tags via LeadTag join table
βœ“ Multi-tenant por agencia con comprobaciΓ³n de membership
βœ“ Seed data lista para emulador local
πŸ”₯ Deploy a producciΓ³n
$ npx -y firebase-tools@latest deploy --only dataconnect
βœ“ Deployed schema to Cloud SQL (us-central1)
βœ“ Deployed connector: cultivaleads-connector
βœ“ 4 queries Β· 9 mutations indexed