Data Warehouse: Filevine Report Tables

The Filevine Report Tables address a key limitation in Filevine's data structure: certain fields in the Filevine UI do not trigger webhook updates, and others are not accesible via the Filevine API. To fill these gaps, Neodeluxe Data Warehouse supports ingestion of scheduled Filevine reports.

This system uses an event-driven architecture to automatically load Filevine-generated CSV reports into dedicated tables in the warehouse. , using an event-driven architecture that reacts to report delivery in S3. The reports are processed and stored in client-specific schemas, ensuring completeness and accessibility.

How It Works

1. Filevine generates the report on a defined schedule.2. Filevine sends the report to a designated S3 bucket via the Data Connector.3. An S3 event triggers a shared EventBridge (also used for Audit reports).4. The event launches a Step Function that processes and ingests the report into Aurora PostgreSQL.

This mechanism allows bulk ingestion of structured data directly from reports into the Data Warehouse.

Key Features

This section covers the main features of Filevine Report Tables—what reports are supported, current limits, system setup needs, and how updates work.

• Supported Reports

  • Filevine Report: List of Contacts.
  • DW Table: reports.fvdw_ProjectContacts.

• Report Tables Limitations

  • Supports only one report type initially: List of Contacts.
  • Supports only three columns: person_id, project_id, and project_roles.
  • Any schema changes (e.g., new columns or report types) require code changes.

• Report Tables Requirements

To use this feature, the following are required:

• An AWS S3 bucket accessible to Filevine’s Data Connector.
• A top-level folder within the bucket for Filevine reports.
• Access to configure Build Reports in Filevine.
• Access to configure AWS services: S3, SNS, SQS, Lambda, and EventBridge.

• Report Tables Update

Filevine Report Tables are updated on a schedule, unlike standard DW tables that update based on events. The update frequency is set when creating the report in Filevine and can be: Up to three times per day, daily, weekly or monthly.Anyone with access to the saved report can update the schedule at any time.

Process Architecture

Filevine Report Tables introduce a new component in the Data Warehouse stack, designed to ingest report data separately from traditional project data. It listens for new CSV reports in S3 and creates or updates corresponding tables in the reports schema.

This process is independent from the main Data Warehouse loader.

Database Schemas

Two new schemas support this feature:

• reports: Contains tables that directly mirror the structure of each Filevine report.Example: fvdw_ProjectContacts
   
• reports_metadata: Stores metadata for each report table to track changes, especially in primary identifiers.Example: fvdw_ProjectContacts

Database Standard Schemas Structure

• reports.fvdw_ProjectContacts

Stores actual data from the Filevine List of Contacts report.

[
  {"name": "personId", "type": "NUMERIC"},
  {"name": "projectId", "type": "NUMERIC"},
  {"name": "projectRoles", "type": "TEXT ARRAY"}
]

• reports_metadata.fvdw_ProjectContacts

Stores metadata related to the data ingestion.

[
  {"name": "versionId", "type": "INTEGER GENERATED ALWAYS AS IDENTITY"},
  {"name": "recordCount", "type": "NUMERIC"},
  {"name": "rowCount", "type": "NUMERIC"},
  {"name": "columnCount", "type": "NUMERIC"},
  {"name": "dwLastUpdatedAt", "type": "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"}
]