# Inbound Realtime N8N to PostgreSQL

<figure><img src="/files/sIyidg1XsAP9cBb2l20L" alt=""><figcaption></figcaption></figure>

<table><thead><tr><th width="234">Method</th><th>GET</th></tr></thead><tbody><tr><td><strong>URL</strong></td><td><a href="https://api.sleekflow.io/api/contact?limit=100&#x26;offset=0">https://api.sleekflow.io/api/contact?limit=100&#x26;offset=0</a></td></tr><tr><td><strong>URL</strong></td><td><a href="https://api.sleekflow.io/api/conversation/all?limit=100&#x26;offset=0&#x26;status=closed">https://api.sleekflow.io/api/conversation/all?limit=100&#x26;offset=0&#x26;status=closed</a></td></tr><tr><td><strong>URL</strong></td><td><a href="https://api.sleekflow.io/api/conversation/all?limit=100&#x26;offset=0&#x26;status=open">https://api.sleekflow.io/api/conversation/all?limit=100&#x26;offset=0&#x26;status=open</a></td></tr><tr><td><strong>Send Headers</strong></td><td>ON</td></tr><tr><td><strong>Header Parameters</strong></td><td>X-Sleekflow-Api-Key</td></tr></tbody></table>

{% code title="chat.js" overflow="wrap" lineNumbers="true" %}

```javascript
// Function to format the timestamp to 'YYYY-MM-DD HH:MM:SS'
function formatTimestamp(timestamp) {
  if (!timestamp) return null; // Return null if missing
  const date = new Date(timestamp);
  if (isNaN(date.getTime())) return null; // Return null for invalid date

  // Extract the year, month, day, hours, minutes, and seconds
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0'); // Month is 0-based
  const day = String(date.getDate()).padStart(2, '0');
  const hours = String(date.getHours()).padStart(2, '0');
  const minutes = String(date.getMinutes()).padStart(2, '0');
  const seconds = String(date.getSeconds()).padStart(2, '0');

  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}

// Function to map lastChannelIdentityId to specific labels
function mapChannelIdentity(lastChannelIdentityId) {
  if (!lastChannelIdentityId) return null;

  if (lastChannelIdentityId.includes("409534172246677")) {
    return "fb_kyzn_id";
  } else if (lastChannelIdentityId.includes("17841468769957389")) {
    return "ig_kyzn_id";
  } else if (lastChannelIdentityId.includes("19190")) {
    return "fb_kuningan";
  } else if (lastChannelIdentityId.includes("17841453892878958")) {
    return "ig_kuningan";
  } else if (lastChannelIdentityId.includes("19191")) {
    return "fb_bsd";
  } else if (lastChannelIdentityId.includes("17841450110322840")) {
    return "ig_bsd";
  } else if (lastChannelIdentityId.includes("113207553436200")) {
    return "ig_kyzn_life";
  } else if (lastChannelIdentityId.includes("110429021829147")) {
    return "ig_kyzn_preschool";
  } else if (lastChannelIdentityId.includes("6285174420088")) {
    return "whatsapp";
  } else {
    return "whatsapp";
  }
}

// Function to map mremarks based on the value of sremarks
function mapMremarks(sremarks) {
  if (["fb_kyzn_id", "ig_kyzn_id", "fb_kuningan", "ig_kuningan", "fb_bsd", "ig_bsd"].includes(sremarks)) {
    return "dm";
  } else {
    return "wa"; // Default or fallback value
  }
}

// Function to map branch based on mremarks value
function mapBranch(sremarks) {
  const branchMapping = {
    fb_bsd: "KYZN BSD",
    ig_bsd: "KYZN BSD",
    fb_kuningan: "KYZN Kuningan",
    ig_kuningan: "KYZN Kuningan"
  };
  return branchMapping[sremarks] || "KYZN"; // Default branch value
}

// Function to convert text to Capitalized Case
function capitalizeText(text) {
  if (!text) return null; // Handle missing text
  return text
    .toLowerCase()
    .split(" ")
    .map(word => word.charAt(0).toUpperCase() + word.slice(1))
    .join(" ");
}

// List of names to check for exclusion in contact_owner
const excludedOwners = new Set([
  "KYZN", "Diadre Marketing", "Sales BSD", "Sales KNG", 
  "Lutfi CA", "FO BSD", "FO Kuningan", "Siska Permata", "Admin KYZN"
]);

// Get the input from the previous node
const data = items;

// Transform the data to the desired format with safe checks for undefined fields
const result = data.map(item => {
  const sremarks = mapChannelIdentity(item.json.lastChannelIdentityId);
  const mremarks = mapMremarks(sremarks);
  const rawContactOwner = item.json.assignee?.userInfo?.displayName || item.json.assignedTeam?.teamName || null;
  const contactOwner = excludedOwners.has(rawContactOwner) ? null : rawContactOwner;

  return {
    id: item.json.userProfile?.id || null,
    conversationId: item.json.conversationId || null,
    name: capitalizeText(item.json.userProfile?.firstName || null), // Convert to Capitalized Case
    phone: item.json.userProfile?.whatsAppAccount?.phone_number || null,
    created_at: formatTimestamp(item.json.userProfile?.createdAt),
    updated_at: formatTimestamp(item.json.userProfile?.updatedAt),
    last_contact_from_customer: formatTimestamp(item.json.userProfile?.lastContactFromCustomers),
    contact_owner: contactOwner, // Apply exclusion rule
    contact_owner_email: item.json.assignee?.userInfo?.email || null,
    last_channel: item.json.lastMessageChannel || 'whatsapp',
    sremarks_id: item.json.lastChannelIdentityId || null,
    sremarks: sremarks || 'whatsapp',
    mremarks: mremarks || 'wa',
    branch: mapBranch(sremarks) // Map branch based on sremarks
  };
});

// Return the result to the next node
return result;
```

{% endcode %}

{% code title="convert.js" overflow="wrap" lineNumbers="true" %}

```javascript
// Function to format the timestamp to 'YYYY-MM-DD HH:MM:SS'
function formatTimestamp(timestamp) {
  const date = timestamp ? new Date(timestamp) : new Date(); // Use current date-time if missing

  // Extract the year, month, day, hours, minutes, and seconds
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0'); // Month is 0-based
  const day = String(date.getDate()).padStart(2, '0');
  const hours = String(date.getHours()).padStart(2, '0');
  const minutes = String(date.getMinutes()).padStart(2, '0');
  const seconds = String(date.getSeconds()).padStart(2, '0');

  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}

// List of names to check for exclusion
const excludedOwners = new Set([
  'KYZN', 'Diadre Marketing', 'Sales BSD', 'Sales KNG', 
  'Lutfi CA', 'FO BSD', 'FO Kuningan', 'Siska Permata', 'Admin KYZN'
]);

// Function to map sremarks_id to specific labels
function mapSremarksId(sremarks_id) {
  if (!sremarks_id) return "whatsapp"; // Default jika tidak ada id
  
  const identityMap = {
    "409534172246677": "fb_kyzn_id",
    "17841468769957389": "ig_kyzn_id",
    "19190": "fb_kuningan",
    "17841453892878958": "ig_kuningan",
    "19191": "fb_bsd",
    "17841450110322840": "ig_bsd",
    "113207553436200": "ig_kyzn_life",
    "110429021829147": "ig_kyzn_preschool",
    "6285174420088": "whatsapp"
  };

  return identityMap[sremarks_id] || "whatsapp";
}

// Function to determine branch based on sremarks
function mapBranch(sremarks) {
  const branchMapping = {
    fb_bsd: "KYZN BSD",
    ig_bsd: "KYZN BSD",
    fb_kuningan: "KYZN Kuningan",
    ig_kuningan: "KYZN Kuningan"
  };
  return branchMapping[sremarks] || "KYZN"; // Default branch value
}

// Function to determine mremarks based on sremarks
function mapMremarks(sremarks) {
  const dmSources = ["fb_kyzn_id", "ig_kyzn_id", "fb_kuningan", "ig_kuningan", "fb_bsd", "ig_bsd"];
  return dmSources.includes(sremarks) ? "dm" : "wa"; // Default fallback
}

// Function to convert text to Capitalized Case
function capitalizeText(text) {
  if (!text) return null; // Handle missing text
  return text
    .toLowerCase()
    .split(" ")
    .map(word => word.charAt(0).toUpperCase() + word.slice(1))
    .join(" ");
}

// Get the input from the previous node
const data = items;

// Transform the data, converting all relevant timestamp fields
const result = data.map(item => {
  const contactOwnerName = item.json.ContactOwnerName || null;
  const adSourceId = item.json.AdSourceID || null;
  const leadSource = item.json.LeadSource || null;
  const phoneNumber = item.json.PhoneNumber || null;
  const sremarks_id = item.json.sremarks_id || null;

  let source = leadSource;
  let sremarks = item.json.sremarks || null;
  let medium = 'sleekflow'; // Default to 'sleekflow'
  let mremarks = item.json.mremarks || null;

  // Apply conditions
  if (adSourceId) {
    // Jika ad_source_id tidak null
    source = 'meta_ads';
    sremarks = adSourceId;
    mremarks = 'wa';
  } else {
    // Jika ad_source_id null, cek sremarks_id
    sremarks = mapSremarksId(sremarks_id);
    source = 'social_media';
    mremarks = mapMremarks(sremarks);
  }

  return {
    id: item.json.id || null,
    phone: phoneNumber,
    language: item.json.Language || null,
    interest: item.json.Interest || null,
    interest_remarks: item.json['interest remarks'] || null, // Corrected for property with space
    last_contact: formatTimestamp(item.json.LastContact),
    last_contact_from_customer: formatTimestamp(item.json.LastContactFromCustomers),
    last_contact_from_company: formatTimestamp(item.json.LastContactedFromCompany),
    last_contact_from_user: formatTimestamp(item.json.LastContactedFromUser),
    created_at: formatTimestamp(item.json.CreatedAt),
    updated_at: formatTimestamp(item.json.UpdatedAt),
    last_channel: item.json.LastChannel || null,
    contact_owner: excludedOwners.has(contactOwnerName) ? null : contactOwnerName,
    contact_owner_email: item.json.ContactOwnerEmail || null,
    source: source,
    sremarks: sremarks,
    medium: medium,
    mremarks: mremarks,
    click_id: item.json.AdClickID || null,
    ad_source_url: item.json.AdSourceURL || null,
    ad_source_id: adSourceId,
    name: capitalizeText(item.json.FirstName || 'unknown'),
    branch: mapBranch(sremarks),
    conversationId: item.json.conversationId || null,
    sremarks_id: sremarks_id,
  };
});

// Return the result to the next node
return result;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://work.bahrul.me/work/kyzn/automation/inbound-realtime-n8n-to-postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.