Spreadsheet Upload with FTS Company Matching and Channel Management

Overview

This guide explains how to implement a spreadsheet upload system that matches company names and symbols against the FinTech Studios (FTS) API, then creates or updates channels with the matched entity IDs.

System Architecture

1. Database Schema

Create a PostgreSQL table to store uploaded spreadsheets:

CREATE TABLE data_uploads (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  display_name VARCHAR(255),
  spreadsheet_type VARCHAR(50) DEFAULT 'company-channel',
  file_type VARCHAR(10) NOT NULL,
  status VARCHAR(20) DEFAULT 'uploaded',
  column_headers TEXT[] NOT NULL,
  name_column_index INTEGER,
  symbol_column_index INTEGER,
  entity_id_column_index INTEGER,
  sp_id_column_index INTEGER,
  total_rows INTEGER NOT NULL,
  uploaded_at TIMESTAMP DEFAULT NOW(),
  configured_at TIMESTAMP,
  processed_at TIMESTAMP
);

CREATE TABLE data_upload_rows (
  id SERIAL PRIMARY KEY,
  upload_id INTEGER REFERENCES data_uploads(id) ON DELETE CASCADE,
  row_index INTEGER NOT NULL,
  row_data JSONB NOT NULL
);

CREATE TABLE data_upload_processed_data (
  id SERIAL PRIMARY KEY,
  upload_id INTEGER UNIQUE REFERENCES data_uploads(id) ON DELETE CASCADE,
  total_rows INTEGER,
  matched_count INTEGER,
  error_count INTEGER,
  results JSONB NOT NULL
);

2. File Upload and Parsing

Backend Endpoint: POST /api/data-upload

Accept CSV or Excel files (using xlsx and csv-parser npm packages):

import * as XLSX from 'xlsx';
import csv from 'csv-parser';

app.post('/api/data-upload', upload.single('file'), async (req, res) => {
  const file = req.file;
  const spreadsheetType = req.body.spreadsheetType || 'company-channel';

  // Parse file based on type
  let rows = [];
  let headers = [];

  if (file.mimetype === 'text/csv') {
    // Parse CSV
    const stream = Readable.from(file.buffer);
    stream.pipe(csv()).on('data', (row) => {
      if (!headers.length) headers = Object.keys(row);
      rows.push(Object.values(row));
    });
  } else {
    // Parse Excel
    const workbook = XLSX.read(file.buffer);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });
    headers = data[0];
    rows = data.slice(1);
  }

  // Store in database
  const upload = await storage.createDataUpload({
    userId: req.user.id,
    fileName: file.originalname,
    spreadsheetType,
    fileType: file.mimetype,
    columnHeaders: headers,
    totalRows: rows.length
  });

  // Store rows
  for (let i = 0; i < rows.length; i++) {
    await storage.createDataUploadRow({
      uploadId: upload.id,
      rowIndex: i,
      rowData: rows[i]
    });
  }

  res.json({ success: true, uploadId: upload.id, totalRows: rows.length });
});

3. Column Configuration

Frontend Component

Create dropdowns for users to map columns:

<Select onValueChange={(value) => setNameColumnIndex(parseInt(value))}>
  <SelectTrigger>
    <SelectValue placeholder="Select name column" />
  </SelectTrigger>
  <SelectContent>
    <SelectItem value="none">None</SelectItem>
    {columnHeaders.map((header, index) => (
      <SelectItem key={index} value={index.toString()}>
        {index}: {header}
      </SelectItem>
    ))}
  </SelectContent>
</Select>

Column Types

Name Column (Required): Company name for FTS API search
Symbol Column (Optional): Stock symbol for enhanced matching
Entity ID Column (Optional): Direct FTS Entity ID (bypasses name matching)
SP ID Column (Optional): S&P Company ID for direct linking

Backend Endpoint: PUT /api/data-uploads/:id/configure

app.put('/api/data-uploads/:id/configure', async (req, res) => {
  const { nameColumnIndex, symbolColumnIndex, entityIdColumnIndex, spIdColumnIndex } = req.body;

  await storage.updateDataUpload(uploadId, {
    nameColumnIndex,
    symbolColumnIndex,
    entityIdColumnIndex,
    spIdColumnIndex,
    status: 'configured',
    configuredAt: new Date()
  });

  res.json({ success: true });
});

4. FTS Company Matching Logic

Backend Endpoint: POST /api/data-uploads/:id/process-company-channel

Step 1: Name Cleaning Function

function cleanCompanyName(name) {
  if (!name) return '';

  let cleaned = name.trim()
    .replace(/\s+/g, ' ')
    .replace(/\.$/, '')
    .replace(/,\s*(Inc|LLC|Corp|Ltd|Co|SA|AG|SE|NV|PLC|SpA|GmbH|KK|Plc|AB)\.?$/i, '');

  // Remove common suffixes but preserve hyphens in company names
  const suffixes = [
    'Corporation', 'Incorporated', 'Company', 'Limited', 
    'Holdings', 'Group', 'International', 'Industries'
  ];

  for (const suffix of suffixes) {
    const regex = new RegExp(`\\s+${suffix}\\s*$`, 'i');
    cleaned = cleaned.replace(regex, '');
  }

  return cleaned.trim();
}

Step 2: FTS API Search

FTS API Endpoint: `GET https://api.fintechstudios.com/entity/search/{companyName}?count=20&include=company`

Authentication: Bearer token (OAuth2)

async function searchFTSEntity(companyName, credentials) {
  const cleanedName = cleanCompanyName(companyName);
  const searchUrl = `https://api.fintechstudios.com/entity/search/${encodeURIComponent(cleanedName)}?count=20&include=company`;

  // Ensure token is fresh
  if (isTokenExpired(credentials.tokenExpiry)) {
    credentials.accessToken = await refreshFintechToken(credentials);
  }

  const response = await fetch(searchUrl, {
    headers: {
      'Authorization': `Bearer ${credentials.accessToken}`,
      'x-api-key': credentials.apiKey
    }
  });

  if (!response.ok) {
    throw new Error(`FTS API error: ${response.status}`);
  }

  return await response.json();
}

Step 3: Matching Logic

async function processCompanyChannel(uploadId, credentials) {
  const upload = await storage.getDataUpload(uploadId);
  const rows = await storage.getDataUploadRows(uploadId);

  const processedResults = [];
  let matchedCount = 0;
  let errorCount = 0;

  for (const row of rows) {
    const rawName = row.rowData[upload.nameColumnIndex];
    const rawSymbol = row.rowData[upload.symbolColumnIndex];
    const entityId = row.rowData[upload.entityIdColumnIndex];
    const spId = row.rowData[upload.spIdColumnIndex];

    try {
      let matchFound = false;
      let result = { ...row.rowData, rawCompanyName: rawName, rawSymbol };

      // Priority 1: Direct Entity ID lookup
      if (entityId) {
        const entityResponse = await fetch(
          `https://api.fintechstudios.com/entity/${entityId}`,
          { headers: { 'Authorization': `Bearer ${credentials.accessToken}` }}
        );

        if (entityResponse.ok) {
          const entity = await entityResponse.json();
          result.ftsEntityId = entity.id;
          result.ftsEntityName = entity.name;
          result.ftsEntitySymbol = entity.symbol;
          result.matchResult = 'Matched (Direct Entity ID)';
          matchFound = true;
          matchedCount++;
        }
      }

      // Priority 2: Name-based search
      if (!matchFound && rawName) {
        const cleanedName = cleanCompanyName(rawName);
        const searchResult = await searchFTSEntity(cleanedName, credentials);

        if (searchResult.entities && searchResult.entities.length > 0) {
          const entity = searchResult.entities[0]; // Use first match
          result.ftsEntityId = entity.id;
          result.ftsEntityName = entity.name;
          result.ftsEntitySymbol = entity.symbol;
          result.matchResult = 'Matched';
          result.processedName = cleanedName;
          matchFound = true;
          matchedCount++;
        }
      }

      // No match found
      if (!matchFound) {
        result.matchResult = 'Not Matched';
        result.ftsEntityId = null;
        errorCount++;
      }

      processedResults.push(result);

    } catch (error) {
      processedResults.push({
        ...row.rowData,
        matchResult: 'Error',
        errorMessage: error.message
      });
      errorCount++;
    }
  }

  // Store processed data
  await storage.updateDataUpload(uploadId, {
    status: 'processed',
    processedAt: new Date()
  });

  await storage.saveProcessedData(uploadId, {
    totalRows: rows.length,
    matchedCount,
    errorCount,
    results: processedResults
  });

  return { matchedCount, totalRows: rows.length, results: processedResults };
}

5. Channel Creation and Management

Channel Definition Structure

interface ChannelDefinition {
  channel: {
    owner: string;      // FTS User ID (e.g., "auth0|62cf...")
    public: true;       // Always true
    name: string;       // Channel name
    description: string;
    id?: number;        // Include for updates, omit for new channels
    stages: [{
      not: false;
      entities: Array<{
        type: "entity";
        value: number;   // FTS Entity ID
        options: {
          relevance: "high";
        };
      }>;
    }];
  };
}

Create Channel Function

function createChannelDefinition(channelName, userId, entityIds, channelId = null) {
  const timestamp = new Date().toLocaleDateString('en-US', {
    year: 'numeric',
    month: 'long',
    day: 'numeric',
    hour: 'numeric',
    minute: '2-digit',
    hour12: true
  });

  const channelEntities = entityIds.map(entityId => ({
    type: "entity",
    value: entityId,
    options: { relevance: "high" }
  }));

  const definition = {
    channel: {
      owner: userId,
      public: true,
      name: channelName,
      description: `Auto-generated channel | Created: ${timestamp}`,
      stages: [{
        not: false,
        entities: channelEntities
      }]
    }
  };

  // Include ID only for updates
  if (channelId) {
    definition.channel.id = channelId;
  }

  return definition;
}

Create New Channel

FTS API Endpoint: `POST https://api.fintechstudios.com/channel`

async function createChannel(channelName, entityIds, credentials) {
  const definition = createChannelDefinition(
    channelName,
    credentials.userId,
    entityIds
  );

  const response = await fetch('https://api.fintechstudios.com/channel', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${credentials.accessToken}`,
      'x-api-key': credentials.apiKey,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(definition)
  });

  if (!response.ok) {
    throw new Error(`Failed to create channel: ${response.status}`);
  }

  const result = await response.json();
  const channelId = result.id || result.channel?.id;

  // Store channel ID in local database
  await storage.createChannel({
    userId: req.user.id,
    channelId: channelId,
    name: channelName,
    description: definition.channel.description
  });

  return { channelId, ...result };
}

Update Existing Channel

FTS API Endpoint: `PUT https://api.fintechstudios.com/channel`

async function updateChannel(channelId, channelName, entityIds, credentials) {
  const definition = createChannelDefinition(
    channelName,
    credentials.userId,
    entityIds,
    channelId  // Include existing channel ID
  );

  const response = await fetch('https://api.fintechstudios.com/channel', {
    method: 'PUT',
    headers: {
      'Authorization': `Bearer ${credentials.accessToken}`,
      'x-api-key': credentials.apiKey,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(definition)
  });

  if (!response.ok) {
    throw new Error(`Failed to update channel: ${response.status}`);
  }

  return await response.json();
}

6. Frontend Results Display

Display processed data with channel creation controls

function ProcessedDataView({ uploadId, processedData }) {
  const [channelName, setChannelName] = useState('');
  const [selectedChannelId, setSelectedChannelId] = useState(null);

  const matchedEntities = processedData.results
    .filter(r => r.ftsEntityId)
    .map(r => r.ftsEntityId);

  async function handleCreateChannel() {
    const response = await fetch('/api/channels/create', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        channelName,
        entityIds: matchedEntities
      })
    });

    const result = await response.json();
    setSelectedChannelId(result.channelId);
  }

  async function handleUpdateChannel() {
    await fetch(`/api/channels/${selectedChannelId}/update`, {
      method: 'PUT',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        channelName,
        entityIds: matchedEntities
      })
    });
  }

  return (
    <div>
      <div className="stats">
        <p>Total Rows: {processedData.totalRows}</p>
        <p>Matched: {processedData.matchedCount}</p>
        <p>Not Matched: {processedData.errorCount}</p>
      </div>

      <div className="channel-controls">
        <Input
          value={channelName}
          onChange={(e) => setChannelName(e.target.value)}
          placeholder="Enter channel name"
        />

        {selectedChannelId ? (
          <Button onClick={handleUpdateChannel}>
            Update Channel {selectedChannelId}
          </Button>
        ) : (
          <Button onClick={handleCreateChannel}>
            Create New Channel
          </Button>
        )}
      </div>

      <Table>
        {/* Display processed results */}
      </Table>
    </div>
  );
}

7. FTS Authentication

OAuth2 Token Management

async function refreshFintechToken(credentials) {
  const response = await fetch('https://fintechstudios.us.auth0.com/oauth/token', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      grant_type: 'client_credentials',
      client_id: credentials.clientId,
      client_secret: credentials.clientSecret,
      audience: 'https://api.fintechstudios.com'
    })
  });

  const data = await response.json();
  const tokenExpiry = new Date(Date.now() + data.expires_in * 1000);

  // Store new token
  await storage.updateCredentials({
    accessToken: data.access_token,
    tokenExpiry
  });

  return data.access_token;
}

function isTokenExpired(tokenExpiry) {
  if (!tokenExpiry) return true;
  return new Date(tokenExpiry) <= new Date();
}

8. Required NPM Packages

{
  "dependencies": {
    "xlsx": "^0.18.5",
    "csv-parser": "^3.0.0",
    "multer": "^1.4.5-lts.1"
  }
}

9. Frontend User Flow

Upload File: User selects CSV/Excel file with company data
Configure Columns: User maps Name, Symbol, Entity ID, and SP ID columns
Process Data: System searches FTS API and matches companies
Review Results: User sees matched/unmatched companies
Create Channel: User enters channel name and creates/updates channel
Store Channel ID: System stores the channel ID for future updates

10. Best Practices

Name Cleaning: Always clean company names before searching (remove suffixes, normalize whitespace)
Token Management: Check token expiry before every API call and refresh if needed
Error Handling: Log all API errors and display user-friendly messages
Direct Entity ID: When Entity ID column is provided, use direct lookup (faster and more accurate)
Symbol Matching: Use symbols for enhanced matching accuracy when available
Batch Processing: Process rows in batches to avoid API rate limits
Channel Updates: Always include the channel ID when updating to preserve existing channel data
User Feedback: Show progress indicators during processing (e.g., "Processing row 45/100")

11. Testing Checklist

[ ] CSV file upload and parsing
[ ] Excel file upload and parsing
[ ] Column configuration saves correctly
[ ] Name cleaning function works properly
[ ] FTS API search returns entities
[ ] Direct Entity ID lookup bypasses name search
[ ] Token refresh works when expired
[ ] Matched companies display correctly
[ ] Unmatched companies display correctly
[ ] Channel creation returns valid channel ID
[ ] Channel update preserves existing data
[ ] Error handling displays user-friendly messages

Additional Notes

The FTS API uses OAuth2 authentication with client credentials flow
All FTS API calls require both Bearer token and x-api-key header
Channel definitions must include owner field with FTS User ID
Entity IDs in channel definitions must be valid FTS entity IDs
Channel updates require the channel ID in the definition object
Token expiry should be checked before every API call to prevent 401 errors

Last update: December 22, 2025