New Rich Text Editing Capabilities in Forms

Customize the appearance of your Form text with our new rich text editing capabilities. Enabling this on your text columns will give you access to exciting new formatting options previously unavailable in Forms such as:

• Bold, Italic, Underlined text

• Checklists

• Headers

• Hyperlinks

• etc.

For more information on how to make a visual impact with our new rich text editing capabilities, please review the documentation here.

Editable GUIDs

A GUID is a globally unique identifier, formatted as a 128-bit text string, that represents a unique ID. All Cinchy Tables and Columns have a GUID.

You now have the ability to display and edit table and column GUIDs within the Design Table screen.

This feature is particularly useful when deploying between Cinchy instances.

For example, in a model deployment, you must have matching GUIDs on your columns in order for them to properly load between environment A and environment B. There might be times when these GUIDs don’t automatically match, however, such as if you manually added a new column to environment B and also manually added it to environment A.

In this case, the two columns would have different GUIDs, and the model deployment would fail. With this new capability, however, you can match up conflicting GUIDs to properly load your model.

Polling Event Data Sync

Version 5.4 of the Cinchy platform introduces data polling, which uses the Cinchy Event Listener to continuously monitor and sync data entries from your SQL Server or DB2 server into your Cinchy table. This capability makes data polling a much easier, effective, and streamlined process and avoids implementing the complex orchestration logic that was previous necessary to capture frequently changing data.

You can read more about setting up Data Polling here.

BigInt Upgrade Utility

A mandatory database upgrade script was introduced in v5.2 that increased the number of possible Cinchy IDs that can be generated (read the releases note here). To streamline this process further, we've created a utility to deploy the changes. This should save you valuable time and resources when performing the upgrade, even on large databases.

New Platform Default Timeout

For new environments (or if your setting was previously left blank), we've changed the Cinchy default session timeout from 30 minutes to 7 days. This will keep you logged in and working without interruptions. You can further change or revert this session timeout value in your appsettings.json.

In an IIS deployment, you can find the value in your CinchySSO > appsettings.json

“ConfigSettings”: {
	“AppSettings”: {
		"CinchyAccessTokenLifetime": "7.00:00:00",

In a Kubernetes deployment, you can find the value in your deployment.json file.

// The Cinchy session timeout
"cinchy_session_timeout" : "7.00:00:00",

Enhancements

• We've upgraded our application components to .NET 6.0 to ensure official Microsoft support for another 2 years. See the documentation here for more on .NET 6.

• We've added a silent refresh to the Connections experience to keep your session active while you're on the UI and to keep you working without interruptions.

• Real time data sync will now continue to retry if an "Out of Memory Exception" is thrown, avoiding unnecessary downtime.

• You now have the ability to choose between Debian or Alpine based Docker images when using a Kubernetes deployment of the Cinchy platform to be able to connect to a DB2 data source in Connections. <<<<<<< HEAD

  • When either installing or upgrading your platform, you can use the following Docker image tags for the listener, worker, and connections: =======

  • When either installing or upgrading your platform, you can use the following Docker image tags for the listener, worker, and connections:

ed39076 (TOC cleanup) * Alpine: "5.x.x" * Debian: "5.x.x-debian"

• You now have the option to update the default passwords for Grafana and OpenSearch in a Kubernetes deployment by configuring your deployment.json file. See here for instructions on updating Grafana and here for OpenSearch.

• We've increased the average throughput for CDC subscriptions returning the Cinchy ID, so that it will now be able to process a greater number of events per second. Being able to reliably exceed 1000 events per second, based on the average use case, means that you can leverage the CDC capability for more demanding use cases.

• Before this release, the Files API could only handle files up to 100mb. We've now upped the maximum default file size to 1GB and have added a configurable property to allow you to set your own upload size.

  • In an IIS deployment, you can find the value in your Cinchy > appsettings.json

“ConfigSettings”: {
	“AppSettings”: {
		“MaxRequestBodySize”: 1073741824 // 1g

• In a Kubernetes deployment, you can find the value in your deployment.json file.

// this field defaulted at 1gb, dictates the max file upload size set in the web appsettings.json
"web_max_request_body_size" : 1073741824,

Bug Fixes

• We've fixed an error that occurred when attempting a data sync with conflicting target and source data types in link columns, where the error message would read: Value must be specified from the available options

• We've fixed an issue that was preventing new Connection jobs from starting when a previous job got stuck.

• We've fixed an issue where data syncs would fail if your sync key used a Target Column with a Link Column property that's different from the Primary Linked Column in the table definition.

• We've fixed a bug that was impacting write performance to tables on PostgreSQL with Data Change Notifications enabled.

• We've fixed a "cell entitlements failed" error on Forms that would occur if a Form column contained a single quote in the column name.

• We've fixed an issue on Forms where adding a [Created By] or [Modified By] field would return an error.

• The /healthcheck no longer redirects to the initialization screen during a Cinchy startup, allowing you to properly hit the endpoint.

Another Move Toward Infinite Scalability

We've increased the number of possible Cinchy IDs that can be generated. This in turn allows the creation of more records within one table, so that you can create and manage larger data sets.

Previous Limit: 2,147,483,647 (2^31-1) Cinchy IDs per table

Updated Limit: 9,223,372,036,854,775,807 (2^63-1) Cinchy IDs per table

For backward compatibility with your database, you will need to manually run the below script against your TSQL or PGSQL databases. For instructions on how to run this upgrade, see the documentation here.

Connections Enhancements

• We've expanded our Connections capabilities to support binary file types as a data source. Review the documentation here for more information.

• We've improved the Connections experience by now making it optional to input a username or password when starting a batch sync job. This field can now be left blank if you want to run the job as the currently logged in user.

• We've added new optimizations and quicker processing of Kafka messages for real time data syncs in Connections.

• For added security, any logged password or sensitive parameters from the request details of a SOAP connector data sync is now redacted in the logs.

• Dead messages in the event listener are now written out to the execution errors table for easy collection and querying.

• The Connections experience now supports sourcing file based data sources from Azure Blob Storage and Amazon S3.

General Enhancements

• You now have the option to free up database space by using S3 compatible or Azure Blob Storage for file storage. This is configured in your deployment.json for Kubernetes installations and the appsettings.json in an IIS deployment.

  • If you are upgrading from an earlier v5 version, you can update your previous configuration to take advantage of this. For further instructions, review the documentation here.

• You are now able to run an erasure maintenance job on records that link to the Files table to delete the underlying referenced file.

• We've made improvements to the Files API to avoid cache build up and optimize the API.

• General security fixes and updates.

GraphQL (Beta)

• We've added anonymous API access to GraphQL, no token required.

• We've added write operations to our GraphQL beta, meaning that you can now insert and modify data.

Bug Fixes

• We've fixed a UI instability bug that resulted in the inability to resize view panes in the query designer and difficulty in selecting any cell in the first row of a table. This bug was affecting Chromium users (Google Chrome, Brave, Microsoft Edge, etc.) who had recently updated their internet browsers.

• We've fixed a bug that caused the “Created” column to incorrectly display as the last approved date of the column instead of the column created date.

• You can now add/remove a column from a table that has a columnar index without needing to remove said index entirely. We've also fixed a bug that prevented users from reapplying their columnar index to a table once it had been removed.

• We've fixed an issue where an “Unsupported Function Call” error was raised in certain situations when using the REPLACE function in conditional calculated columns in Connections.

• We've fixed a bug that caused unnecessary updates to the Users table when a user’s Language and Region wasn't set.

• We've fixed a bug that was causing some GUID calculated columns to appear as blank, such as in the Integrated Clients table. If you are experiencing this bug, a manual update on the affected rows, either through the UI or through an UPDATE query, will resolve it.

• We've fixed an issue where the '&' in links was sometimes showing up as 'amp&' in the table view instead. This fix will only appear for customers on Postgres/Microsoft SQL servers 2017 or higher.

• We've fixed an issue where certain Update statements on multi-select link columns were failing to properly update with the link values specified. This bug was affecting statement with long strings done via the API.

• We've fixed a bug that was causing the Event Listener to pick up and process messages from deleted configs from the LIstener Configs table.

• We've fixed a bug that was causing an InvalidOperationException when executing a POST request to a Saved Query API.

• We've fixed a bug that was throwing errors on reconciliation when Data Syncs compared Text Conditional Calculated Columns to Links (PGSQL).

• FOR JSON PATH now works as expected in PostGres deployments.

Table of Contents

New Connector

Kafka

We're continuing to improve our Connections offerings, and we now support Kafka as a data sync target in Connections.

Apache Kafka is an end-to-end event streaming platform that:

• Publishes (writes) and subscribes to (reads) streams of events from sources like databases, cloud services, and software applications.

• Stores these events durably and reliably for as long as you want.

• Processes and reacts to the event streams in real-time and retrospectively.

Event streaming thus ensures a continuous flow and interpretation of data so that the right information is at the right place, at the right time for your key use cases.

For information on setting up data syncs with Kafka as a target, please review the documentation here.

New Inbound Data Format for Connections

Apache AVRO

We've also added support for Apache AVRO (inbound) as a data format and added integration with the Kafka Schema Registry, which helps enforce data governance within a Kafka architecture.

Avro is an open source data serialization system that helps with data exchange between systems, programming languages, and processing frameworks. Avro stores both the data definition and the data together in one message or file. Avro stores the data definition in JSON format making it easy to read and interpret; the data itself is stored in binary format making it compact and efficient.

Some of the benefits for using AVRO as a data format are:

• It's compact;

• It has a direct mapping to/from JSON;

• It's fast;

• It has bindings for a wide variety of programming languages.

For more about AVRO and Kafka, read the documentation here.

For information on configuring AVRO in your platform, review the documentation here.

Custom Results in the Network Map

Focus the results of your Network Map to show only the data that you really want to see with our new URL parameters.

You can now add Target Node, Depth Level, and Max Depth Level Parameters, if you choose.

Example: <base url>/apps/datanetworkvisualizer?targetNode=&maxDepth=&depthLevel=

• Target Node: Using the Target Node parameter defines which of your nodes will be the central node from which all connections branch from.

  • Target Node uses the TableID number, which you can find in the URL of any table.

  • Example: <base url>/apps/datanetworkvisualizer?targetNode=8 will show TableID 8 as the central node

• Max Depths: This parameter defines how many levels of network hierarchy you want to display.

  • Example: <base url>/apps/datanetworkvisualizer?maxDepth=2 will only show you two levels of connections.

• Depth Level: Depth Level is a UI parameter that will highlight/focus on a certain depth of connections.

  • Example: <base url>/apps/datanetworkvisualizer?DepthLevel=1 will highlight all first level network connections, while the rest will appear muted.

The below example visualizer uses the following URL: <base url>/apps/datanetworkvisualizer?targetNode=8&maxDepth=2&depthLevel=1

• It shows Table ID 8 ("Groups") as the central node.

• It only displays the Max Depth of 2 connections from the central node.

• It highlights the nodes that have a Depth Level of 1 from the central node.

Enhancements

• We've increased the length of the [Parameters] field in the [Cinchy].[Execution Log] to 100,000 characters.

• Two new parameters are now available to use in real time syncs that have a Cinchy Table as the target. @InsertedRecordIds() and @UpdatedRecordIds() can be used in post sync scripts to insert and update Record IDs respectively, with the desired value input as comma separated list format.

Bug Fixes

• We've fixed a bug that was preventing some new SSO users belonging to existing active directory groups from seeing tables that they should have access to.

• We've fixed a bug where a Webhook would return a 400 error if a JSON body was provided, and the key was in the query parameter of the HTTP request.

GraphQL

• We continue to optimize our GraphQL beta capabilities by improving memory utilization and performance.

Cinchy Upgrade Utility

The Cinchy Upgrade Utility was previously introduced in v5.2 to facilitate a mandatory INT to BigInt upgrade. This tool will continue to be used in subsequent releases as an easy way to deploy necessary changes to your Cinchy platform.

For version 5.5, you must run the Upgrade Utility to fix a row-breaking issue that could be triggered on cells with over 4000 characters, where you are unable to update any column in your record.

Please review the Utility Guide or Upgrade Guide for further details.

New: Personal Access Tokens

You now have the option to use personal access tokens (PATs) in Cinchy, which are alternatives to using passwords for authentication. Similar to Cinchy Bearer Tokens, you can use a Cinchy PAT to call the Cinchy API as your current user, meaning your associated access controls will be honoured as well. Cinchy PATs, however, have an expiration date of up to 1 year. A single user can have up to 5 PATs active at one time.

For information on setting up, configuring, and managing PATs, please review the documentation here.

MongoDB

We've added MongoDB to our Connections offering as both a source and target connector.

MongoDB is a scalable, flexible NoSQL document database platform known for its horizontal scaling and load balancing capabilities, which has given application developers an unprecedented level of flexibility and scalability.

Review the following documentation to use this new capability in Cinchy:

• Setting up a MongoDB Collection (Event Triggered) as a source connector

• Setting up a MongoDB Collection as a source connector

• Setting up MongoDB as a target connector

• Setting up a MongoDB change stream

Enhancements

• We're continuing to improve our text editor functionality for forms. You can now embed tables and images into your text. We've also made various styling and usability quality of life updates, including the addition of checkbox style lists.

• We've added support for ephemeral volumes in Connections on a Kubernetes deployment. Unlike persistent volumes, ephemeral storage is unstructured and the space is shared between all pods running on a node**;** it allows pods to be started and stopped without being limited to the location of persistent volume. Running more than one pod for Connections per availability zone enables you to effectively leverage auto scaling functionality.

• We've updated the Connections experience to enable more use cases. You can now use CDC parameters in Calculated Columns and use the CinchyID in the sync key in real-time syncs.

• Kafka supports cluster encryption and authentication, which can encrypt data-in-transit between your applications and Kafka. We've added the ability to include this encryption/authentication in the Listener Config when setting up real-time syncs using Kafka.

  • Using this parameter will specify which protocol will be used for communication between client and server. Cinchy currently supports the following options: Plaintext, SaslPlaintext, or SaslSsl.

    • Paintext: Unauthenticated, non-encrypted.

    • SaslPlaintext: SASL-based authentication, non-encrypted.

    • SaslSSL: SASL-based authentication, TLS-based encryption.

  • Review the Listener Config documentation on Kafka here.

• We've improved the implementation of tooltips such that linked columns display the tables that they link to. Hovering over the i symbol on a linked column will show the linked domain and table in the following format: Domain - Table; ex: HR - Employees. You can now also see them in the grid view.

• We've introduced a Retry Configuration for REST API sources and targets. This will automatically retry HTTP Requests on failure based on a defined set of conditions. This capability provides a mechanism to recover from transient errors such as network disruptions or temporary service outages.

  • For more information on using this configuration, refer to the documentation here for REST sources and here for REST targets.

• We've increased the default retention for Prometheus from 5GB to 50GB to allow you to store more metric data at a time.

  • This change is automatically reflected in new v5.5 deployments. Customers on previous v5 versions wishing to implement the change are able to rerun the automation script and deploy the new template to reflect the update.

• To make the Forms experience more responsive and process quicker, we've introduced lazy loading of records while searching. Instead of loading and rendering every form record in the search box, which can be a slow process for use cases with millions of records, lazy loading will initially retrieve a limited number of records. These results can then be further optimized by inputting your Lookup Filter Conditions.

• We've added the ability to pass parameters from a REST response into post sync scripts during both real-time and batch data syncs, allowing you to do more with your REST API data.

  • For an example and instructions on this capability, please refer to the documentation here.

• Data changes in Cinchy (CDC) can now be used to trigger a data sync from a REST API or MongoDB data source to a specified target. This works as an alternative to RunQuery.

  • For more information, please review the documentation here (REST API) and here (MongoDB).

• We've added two new functions, JSON_ESCAPE and URL_ESCAPE, which can be used in Connections to escape parameter values when used in constructing the body of a REST API Request or in the URL

• We've added an Authorization header type for REST API data syncs in Connections. An authorization request header can be used to provide credentials that authenticate a user with a server, allowing access to a protected resource. Selecting this header defines the Header Value as a password field.

Bug Fixes

• We've solved an issue that was causing Connections to get stuck behind long running jobs despite there being capacity to execute. This fix enables predictable execution behavior without stoppage.

• We've fixed an issue in the MatchEngine where execution was failing in versions of Cinchy above 5.2.

• File sourced data syncs will no longer fail, allowing you to run Connection jobs with uploaded files without the risk of a file not found error when auto scaling is enabled.

• To prevent needlessly exhausting Cinchy IDs, the platform will no longer continuously retry to update records that have failed to save. This can sometimes occur when a value causes a calculated field to violate a uniqueness constraint. If the below error appears, you will have to manually update the cell to retry the save.

• We've fixed a bug that was causing the Connections UI to crash if you attempted to run a job while there was an empty parameter in the Info tab (ex: no name or formula)

• We've fixed a bug that would causes images in Forms to sometimes appear with a label above them, using the image's URL as the label's value.

• We've fixed a bug that was forcibly terminating authenticated sessions in Grafana, now allowing you to work without interruptions.

• We've solved an issue where using a form as a child form with file links wouldn't render the link thumbnail correctly in the "edit record" view.

• We've fixed a bug that prevented record updates when multiple users attempted to update a row in too quick of a succession.

• We've fixed an issue where doing delta batch syncs with a REST API target wouldn’t replace the @COLUMN parameter correctly.

• We've fixed a bug in Connections where an Oracle sync target would have the wrong tag in the Config XML.

• We've fixed a bug that was causing a “Listener is running” message to erroneously appear when the status of the listener was actually set to Disabled.

• We've fixed a bug that was preventing REST API real-time sync execution errors from being inserted into the execution errors table.

Deprecation of the k8s.gcr.io Kubernetes Image Repository

The Kubernetes project runs a community-owned image registry called registry.k8s.io in which to host its container images. On April 3rd, 2023, the registry k8s.gcr.io was deprecated and no further images for Kubernetes and related subprojects are being pushed to this location.

Instead, there is a new registry: registry.k8s.io.

New Cinchy Deployments: this change will be automatically reflected in your installation.

For Current Cinchy Deployments: please follow the instructions outlined in the upgrade guide to ensure your components are pointed to the correct image repository.

You can review the full details on this change on the Kubernetes blog.

Connections UI Change to Sync Behaviours Tab

To continuously improve our Connections experience, we've made changes to the Sync Behaviours tab for Full-File data syncs.

• Record behaviour is now presented via radio buttons so that you can see and select options quicker and easier than ever before.

• We've added a new "Conditional" option for Changed Record Behaviours. When Conditional is selected, you will be able to define the conditions upon which an Update should occur. For instance, you can set your condition such that an update will only occur when a "Status" column is changed to Red, otherwise it will ignore the changed record. This new feature provides more granularity on the type of data being synced into your destination and allows for more detailed use cases. For more information on this new function please review the documentation here.

Enhancements

Deployment

We've added support AWS EKS EBS volume encryption for customers wishing to take advantage of industry-standard AES-256 data encryption without having to build, maintain, and secure their own key management infrastructure.

By default, the EKS worker nodes will have a gp3 storage class for new deployments. If you are already running a Cinchy environment: make sure to keep your eks_persistent_apps_storage_class to gp2 within the DevOps automation aws.json file.

If you want to move to gp3 storage or gp3 storage and volume encryption: you will have to delete the existing volumes/pvc's for Kafka, Redis, OpenSearch, Logging Operator and Event Listener with StatefulSets so that ArgoCD can recreate the proper resources.

Should your Kafka cluster pods not come back online after deleting the existing volumes/pvc's, restart the Kafka operators. You can verify the change by running the below command:

kubectl get pvc --all-namespaces

Platform

• Miscellaneous security fixes.

• General CDC performance optimizations.

Connections

• Continuing to increase our data sync capabilities and features, you can now use @CinchyID as a parameter in post sync scripts when the source is from a Cinchy Event (such as the Event Broker, the Event Triggered REST API, and the Event Triggered MongoDB sources). This means that you can now design post sync scripts that take advantage of the unique CinchyID value of your records.

• To better communicate the relationship between the Source and any required Listener Configurations, we've added additional help text to event-based sources to the Source step of a connection. This text will help explain when a listener configuration is required as part of the sync.

• We've expanded on our Cinchy Event Triggered data sync source features (REST API and MongoDB), allowing you more freedom to utilize your data. You now have the ability to reference attributes of the CDC Event in your calculated columns. (Note that syncs making use of this must limit their batch size to 1.)

• To better enable your business security and permission-based needs, you are now able to run the Connections pod under a service account that uses an AWS IAM (Identity and Access Management) role, which is an IAM identity that you can create to have specific permissions and access to your AWS resources. To set up an AWS IAM role for use in Connections, please review the documentation here.

• You are also able to use AWS IAM roles when syncing S3 file or DynamoDB sources in Connections. For more information, please review the "Auth Type" field in the relevant data sync source pages.

• To increase your data sync security and streamline authentication, we've added support for the use of x.509 certificate authentication for MongoDB Collection Sources, MongoDB (Cinchy Event Triggered) Sources, and MongoDB Targets. This new feature can be accessed directly from the Connections UI when configuring your data sync. For more information, please review the configuration pages for MongoDB Collection Source, MongoDB (Cinchy Event Triggered) Source, and MongoDB Targets.

Bug Fixes

Platform

• We've fixed a bug that was causing bearer token authenticated APIs to stop working on insecure HTTP Cinchy environments.

• We've fixed an issue relating to the .NET 6 upgrade that was causing the Event Listener and Worker to not start as a service on IIS in v5.4+ deployments.

• We've fixed a “Column doesn’t exist” error that could occur in PostGres deployments when incrementing a column (ex: changing a column data type from number to text).

• We've fixed a bug where table views containing only a single linked column record would appear blank for users with “read-only” permissions.

Connections

• We've fixed a bug where the Listener Configuration message for a data sync using the MongoDB Event source would return as "running" after it was disabled during an exception event -- the message will now correctly return an error in this case.

• We've fixed a bug that was preventing DELETE actions from occurring when Change Approvals were enabled on a CDC source.

• In continuing to provide useful troubleshooting tools, we've fixed a bug that was preventing dead messages from appearing in the Execution Errors table when errors occurred during the open connection phase of a target. This error may have also occurred when a MongoDB target had a connection string pointing to a non-existent port/server.

• We've fixed a bug that was preventing Action Type column values of "Delete" from working with REST API target Delta syncs.

• We've fixed a data sync issue preventing users from using environment variables or other parameters in connection strings.

• We've fixed a bug in the Polling Event data sync where records would fail with a “unique constraint violation” if both an insert and an update statement happened at nearly the same time. To implement this fix, you need to add the “messageKeyExpression” parameter to your listener config when using the Polling Event as a source.

• We've fixed a bug that was causing data syncs to fail when doing platform event inserts of any type into Salesforce targets.

• We've fixed a bug where using the ID Column in a Snowflake target sync would prevent insert and update operations from working.

• We've fixed a bug where attempting to sync documents using a UUID (Universally Unique IDentifier) as a source ID in a MongoDB Event Triggered batch sync would result in a blank UUID value when saved to a Cinchy table.

Meta-Forms

We've made application stability and quality fixes to Forms, including:

• Custom date formats now work in Grid, Form, and Child Form views.

• A child form that has a Link column reference to a parent record now auto populates with the parent record's identity.

• A space has now been added between multi-select values when displaying a record in an embedded child table.

• Negative numbers can now be entered into Number type inputs on forms.

• We've fixed an issue where updated file attachments on a form would fail to save.

CQL

• We've fixed a bug that was causing a “Can’t be Bound" error when you attempted to use an UPDATE query on a multi-select link column as a user with multiple filters active.

You are currently browsing the Cinchy v5.7 platform documentation. For documentation about other versions of the platform, please navigate to the relevant space(s) via the drop-down menu in the upper left of the page.

What’s the purpose of Data Collaboration?

Data collaboration aims to facilitate the sharing, management, and utilization of data across various departments, teams, and systems within an organization. This approach aims to eliminate data silos and reduce the inefficiencies associated with traditional data integration methods like ETL processes or APIs. Data collaboration enables multiple stakeholders to co-produce and maintain data in a controlled, federated manner. Doing so enhances data quality, speeds decision-making, and allows more agile and scalable business operations. Data collaboration aims to maximize the business value derived from data while minimizing the costs and complexities traditionally associated with data management.

The root causes of IT delay and frustration.

When a new IT project is green-lit, you often pay a hefty fine called the integration tax, where you're continuously building new integrations between applications just to reuse data that's already available in your systems.

Over time, this never-ending cycle of copying data between fragmented apps gets more complex, resulting in delayed launches, budget overruns, and “shadow IT” projects.

Facing the rising costs and complexities of traditional data integration? You're not alone. At Cinchy, we understand that the 'Integration Tax' is a growing burden for modern organizations. As your enterprise expands its technology stack, the maze of required integrations becomes exponentially more intricate and expensive to manage. It's not just about initial setup; maintenance, updates, and reconfigurations add up, consuming a large portion of your IT budget and holding back your agility.

“Every year, as the level of new technologies and digital transformation grows, the amount spent on integration increases. So much so that companies collectively spend $700B globally.”

Source: Large Integration Company

That's where Cinchy's Data Collaboration Platform comes in. By introducing a platform designed for co-production, we radically reduce the need for complex integrations. Our platform enables various departments, teams, and systems to collaborate directly on data, effectively eliminating the need for redundant, copy-based integrations. This federated approach to data management means that you maintain control while empowering co-producers to contribute to a shared, dynamic data landscape. The result? A significant reduction in the 'Integration Tax,' liberating your IT resources to focus on delivering real business value. Say goodbye to old-school integration's crippling costs and complexities, and welcome a new era of scalable, efficient data collaboration.

How can this be fixed?

Using Data "Co-Production" to Accelerate IT Delivery

With data collaboration, you shift your approach from copy-based integration for sharing back and forth between collaborators to using an access-based real-time approach.

For every co-production use case built using cinchy, you're avoiding what otherwise would have been a bespoke integration-heavy solution. Also, individual solutions now "pay it forward" by liberating relevant data to participate in future collaboration use cases without any integration effort.

The Cinchy data collaboration platform does for data what the power grid does for electricity. In the same way that buildings no longer need to generate their power thanks to the power grid, with a data collaboration platform, new solutions no longer need to manage, integrate, and protect their own data (Image 4).

Not just connected, but autonomous.

​Simply putting pipes between data silos and centralizing a few housekeeping tasks isn't data collaboration. What that's doing is leading you down a path of managing endless copies. True data collaboration connects and upgrades your data as part of an interconnected autonomous data network.

Autonomous data exists independently of any application. It's self-controlled, self-protected, and self-describing. This creates several benefits compared to traditional app-dependent data, including simplifying cross-application usage and reporting. When you use autonomous data in an interconnected network, wherein individual contributors maintain their roles and priorities as they apply their unique skills and leadership autonomy in a problem-solving process, you get Collaborative Autonomy.

Collaborative Autonomy is the principle underpinning Collaborative Intelligence, the basis of data collaboration and Cinchy.

Individuals aren't homogenized, as in consensus-driven processes, nor equalized through quantitative data processing, as in collective intelligence. Consensus isn't required. Problem resolution is achieved through systematic convergence toward coherent results. Collaborative intelligence relies on Collaborative Autonomy to overcome “the consensus barrier” and succeed where other methods have failed.

Universal access controls and automated data governance

One of the most significant advantages of data collaboration is the ease with which data owners can set universal data access controls at the cellular level and automate data governance.

In effect, it removes the need to maintain access controls within individual apps and centralizes these functions incredibly efficiently.

Compare this with designing and maintaining controls within thousands of apps and systems.

Game Changer: Network Effects for IT Delivery

Data collaboration is a game changer for IT delivery: it produces network effects, where each new solution actually speeds up delivery times and reduces costs.

Network-based designs scale beautifully and become more efficient as they expand. Consider the human brain; its neuroplasticity helps it learn more as it grows. The more interconnected it gets, the better. The neural pathways are reorganizing themselves such that the fewer connections, the higher the intelligence, because information is easier to put to use.

It's the same with data collaboration. The more you connect your data, the simpler your world becomes. And it's also your time machine - you can have applications based on different points in time of your data, and it's all done through network-based design.

There is no going back.

Let's build the connected future, together.

New capabilities

• Cinchy on : You can now deploy Cinchy v5 on the Kubernetes system. Kubernetes is an open-source system that manages and automates the full lifecycle of container-based applications. You now have the ability to deploy Cinchy v5 on Kubernetes, and with it comes a myriad of features that help to simplify your deployment and enhance your scaling. Kubernetes can maximize your container capacity and easily scale up/down with your current operations.

  • : Available to those who deploy on Kubernetes, Fluent Bit collects logs which are then displayed through the OpenSearch visual dashboard, for all pods that write to stdout. This streamlines your search for information by putting the control into your hands and compiling your logs in one easy to access place—you can now easily write a query against all of your logs, in all of your environments. You will have access to a default configuration out of the box, but you can also customize your dashboards as well.

  • and : With the Kubernetes addition, you now have access to Prometheus to collect your metrics. Prometheus records real-time metrics in a time series database used for event monitoring and alerting. You can then create custom dashboards through Grafana to display your data in an easy to use visual that makes reporting on your metrics easy, and even set up push alerts based on your custom needs.

  • : You now have the option to deploy Cinchy on PostgreSQL, an open source alternative to the Microsoft SQL Server that can save you the cost of licensing fees. It's standards-compliant, reliable, highly programmable, and allows for concurrency. Utilizing this deployment makes Cinchy more affordable and scalable. We recommend for AWS users.

  • : Kafka is an open-source event streaming platform. This is designed to act as the middleware that allows for messaging between components through a queuing mechanism.

  • : Redis is currently being used to facilitate a distributed lock using RedLock, which guarantees lock synchronizations across Cinchy instances. It's also a storage location for the execution output when running batch data syncs.

• All components have been transitioned from Log4Net to Serilog.

• The BuildIdentifier property from the appsettings.json will now appear in the healthcheck endpoint at the root level of the JSON payload, with a key of buildidentifier.

Enhancements

• ELMAH was removed from the platform.

• Refactored hidden passwords in initialization.

• Added the ability to ingest S3 data sources for delimited or parquet files.

• Improved performance of the Connection UI when there is a large (250+) number of columns/mappings.

• Optimization of bulk UPSERT performance.

• Added the following UI optimizations for handling large tables: default views set to collapsed, page size limited to 1k records, and added a button for getting the row count. Added the ability for Connections to read/write error files to S3 when an S3 bucket is specified.

• Added accessibility fixes.

• We've added support for PUT, PATCH, and DELETE in UDF Extensions in addition to GET/POST.

• You are able to override a Kafka topic within the appsettings.json for Connections, the Worker, and the Event Listener.

CQL Updates

Bug Fixes

• Fixed an issue where two users approving data could cause the row to become corrupted.

• Addressed a memory leak in query translation issue through the creation of a background task that removes expired objects from the cache.

• Fixed an issue where updating the formula of a non-cached calculated column wouldn’t reflect properly in the Table Columns table.

• Addressed an issue where changing a field in a table with multi-select links results in the removal of the field value from the version history.

• Fixed an API issue when updating UDF columns.

• Fixed an issue where numeric calculated columns that resolved off of a link column's numeric display column wouldn't work.

• Enabling of Content-Type headers to be added for REST API data syncs during GET requests.

Security Fixes

• Added frame-ancestors to the UI to prevent UI redress attacks.

• Implemented HSTS headers for when HTTPS is enabled on Cinchy.

Overview

This section will guide you through the considerations and the prerequisites before deploying version 5 of the Cinchy platform.

The pages in this section include:

• : This page explores your two high-level options for deploying Cinchy, on Kubernetes or on VM, and why Cinchy recommends a Kubernetes deployment. It also walks you through selecting a database to run your deployment on and some sizing considerations.

  • : This page provides Infrastructure (for both Azure and AWS), Cluster, and Platform component overviews for Kubernetes deployments. It also guides you through considerations about your cluster configuration.

  • : This page provides Infrastructure and Platform component overviews for IIS (VM) deployments.

• : This page details important prerequisites for deploying Cinchy v5.

Deployment planning checklist

Use the following checklist when planning for your Cinchy v5 deployment. Each item links to the appropriate documentation page.

• Will you run on ?

The main differences between a Kubernetes based deployment and an IIS deployment are:

• Kubernetes offers the ability to elastically scale.

• IIS limits certain components to running single instances.

• As all caching is in memory in an IIS deployment, if multiple instances are online for redundancy there is point to point communication between them (HTTP requests on the server IPs) required to maintain the cache.

• Performance is better on Kubernetes because of Kafka/Redis

• Prometheus/Grafana and OpenSearch aren't available in an IIS deployment

• The Maintenance CLI runs as a CronJob in Kubernetes while this needs to be orchestrated using a scheduler for an IIS deployment.

• Upgrades are simpler with the container images on Kubernetes.

Kubernetes checklist

If you will be running on Kubernetes, please review the following checklist:

• • Will you be sharing from an existing cluster?

  • Will you be running multiple environments on a single cluster?

  • Will you be using Cinchy's recommended cluster components or your own?

IIS checklist

If you will be running on IIS, please review the following checklist:

• • Specifically, install: ASP.NET Core/.NET Core Runtime & Hosting Bundle

Introduction

This section guides you through the deployment process for Cinchy version 5: from planning all the way through to installation and upgrades.

• If you are looking to deploy Cinchy v5, please start here and read through all the sub-pages:

  • Once you have familiarized yourself with the above documentation, you may move on to either of the below guides, depending on your preference:

• If you are a customer currently on v4 and want to upgrade to v5, start here:

If you have any questions about the processes outlined in this section, please reach out to the Cinchy Support team:

• Via email: support@cinchy.com

• Via phone: 1-888-792-6051

• Through the support portal:

New Connector

• A new connector has been added to the Cinchy Connections experience: you can now use as a source and target connector when performing data syncs. You can review the documentation on connecting as a source and as a target .

GraphQL API Beta Release

• Our GraphQL API provides a complete and understandable description of your data and gives you the power to ask for exactly what data you need and nothing more all in a single request, while leveraging the existing ecosystem of GraphQL developer tools.

• This is a beta release that offers read-only queries. Future releases will include more query features and mutation support (writes).

Enhancements

Connections

• Performing Data Sync from Cinchy to Salesforce no longer requires write access to the sync key column. This means that you can maintain your Salesforce environment and security protocols without needing to either modify them or create additional attributes, for your sync to work.

Data Browser

• We've added WCAG 2.1 AA Accessibility fixes to improve screen-reader performance and keyboard navigation accessibility.

• We’ve implemented a new loading screen for when Cinchy is installing and initializing.

Meta Forms

• We've improved the performance of Meta Forms by reducing the rendering time and adding visual guides to help you see which form sections have completed loading.

• Date fields with custom display formats will now render correctly, as opposed to showing up in mm/dd/yyyy format by default.

• In forms that have a 1:1 parent/child hierarchy, we've added the option to render the child form as a flattened form, instead of in a table grid.

• To improve UI consistency across Forms, the record selection drop down will now appear even if no records exist in the destination table.

CQL Updates

Bug Fixes

• We've fixed an error where scrolling in a table with a file column in certain situations prevents the UI from rendering all the data.

• We've fixed an issue where sorting by columns with a ‘%’ in the column name caused the rows not to sort correctly in the UI.

• We've fixed a bug in Meta Forms that prevented queries in child form filters from working as expected when using OR conditions.

Infrastructure configuration (on cluster)

The diagram below shows a high level overview of a possible Infrastructure diagram with components on the cluster, but your specific configuration may vary (Image 1).

AWS infrastructure configuration (outside cluster)

When deploying Cinchy version 5 on Kubernetes, you may deploy via Amazon Web Services (AWS). The diagram below shows a high level overview of a possible AWS Infrastructure with components outside the cluster, but your specific configuration may vary (Image 2).

Infrastructure component overview

Azure infrastructure configuration (outside cluster)

When deploying Cinchy v5 on Kubernetes, you may deploy via Microsoft Azure. The diagram below shows a high level overview of possible Azure Infrastructure with components outside the cluster, but your specific configuration may vary (Image 3).

Infrastructure component overview

Cluster level component overview

The following highlighted area provides a high-level overview of cluster level components used when deploying Cinchy on Kubernetes and what versions they're running.

These are created once per cluster. Clients may choose to run these components outside of the cluster or replace with their own comparable components. This diagram shows them in the cluster (Image 4).

Cluster level components

These are created once per cluster. Clients may choose to run these components outside of the cluster or replace with their own comparable components.

Cluster configuration

Before you deploy Cinchy on Kubernetes, consider the following about your cluster configuration :

• How many clusters will you need?

• Will you be sharing from an existing cluster?

• Will you be running multiple environments on a single cluster?

Instance component overview

Each Cinchy instance uses the following components to either provide an experience to users/applications or connect data in/out of Cinchy. You can deploy multiple Cinchy instances per cluster, so these components will repeat for each environment.

The following highlighted area provides a high-level overview of instance level components used when running Cinchy on Kubernetes (Image 5).

• Connections: Use the Cinchy Connections experience to create data syncs in/out of the platform. It features persistent storage.

• Data Browser: Cinchy’s data collaboration platform features a Universal Data Browser that allows users to view, change, analyze, and otherwise interact with all data on the network. The Data Browser even enables non-technical business users to manage and update data, build models, and set controls, all through an easy and intuitive UI.

• Identity Provider: An Identity Provider (IdP) creates and manages user credentials and associated identity attributes. Cinchy uses IdPs authentication services to authenticate end-users.

GitOps

Once you set up the configurations, ArgoCD automates the deployment of the desired application states into your specified target environments. Implemented as a Kubernetes controller, it continuously monitors running applications and compares the current, live state against the desired target state (as specified in your repositories).

Kubernetes vs IIS

When choosing to deploy Cinchy version 5, you must decide whether to deploy via Kubernetes or on a VM (IIS).

is an open-source system that manages and automates the full lifecycle of container-based applications. You now have the ability to deploy Cinchy v5 on Kubernetes, which helps to simplify your deployment and enhance your scaling. Kubernetes can maximize your container capacity and scale up/down with your current operations.

• Grafana, OpenSearch, OpenSearch Dashboard: Working together, these three applications provide a visual logging dashboard for all the information coming in from your database pods. This streamlines your search for information by putting the control into your hands and compiling your logs in one easy to access place — you can now write a query against all your logs, in all your environments. You will have access to a default configuration out of the box, but you can also customize your dashboards as well.

• Prometheus: With the Kubernetes addition, you now have access to Prometheus for your metrics dashboard. Prometheus records real-time metrics in a time series database used for event monitoring and alerting. You can then create custom dashboards to display your data in an easy to use visual that makes reporting on your metrics easy, and even set up push alerts based on your custom needs.

You also have the option to run Cinchy on , which was the traditional deployment method before Cinchy v5. Internet Information Services (IIS) for Windows Server is a flexible, secure and manageable Web server for hosting anything on the Web.

Choose a database

Before deploying Cinchy v5, you must select which database you want to use.

The following list outlines the supported databases for Kubernetes Deployments.

For IIS Deployments

Microsoft SQL Server

• Microsoft SQL Server is a relational database management system. As a database server, it's a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

• Microsoft Azure SQL Database is a managed cloud database provided as part of Microsoft Azure. It runs on a cloud computing platform, and access to it's provided as a service. Managed database services take care of scalability, backup, and high availability of the database.

• SQL Managed Instance is a managed, cloud-based, always up-to-date SQL instance that combines broad SQL Server engine compatibility with the benefits of a fully managed PaaS.

Amazon Aurora

• Amazon Aurora (Aurora) is a fully managed relational database engine that's compatible with MySQL and PostgreSQL. It combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. Aurora is part of the managed database service Amazon Relational Database Service (Amazon RDS). Amazon RDS is a cloud web service that makes it easier to set up, operate, and scale a relational database.

PostgreSQL

• PostgreSQL is a free and open-source relational database management system emphasizing extensibility and SQL compliance. PostgreSQL comes with features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset.

• Amazon RDS makes it easy to set up, operate, and scale cloud-based PostgreSQL deployments. With Amazon RDS, you can deploy scalable PostgreSQL deployments with cost-efficient and resizable hardware capacity. Amazon RDS manages complex and time-consuming administrative tasks such as PostgreSQL software installation and upgrades; storage management; replication for high availability and read throughput; and backups for disaster recovery. Amazon RDS for PostgreSQL gives you access to the capabilities of the familiar PostgreSQL database engine.

• This is a fully managed and intelligent Azure Database for PostgreSQL. Enjoy high availability with a service-level agreement (SLA) up to 99.99 percent, AI-powered performance optimization, and advanced security. A fully managed database that automates maintenance, patching, and updates. Provision in minutes and independently scale compute or storage in seconds.

Sizing considerations and requirements

Before deploying Cinchy v5, you need to define your sizing requirements.

Sizing

Kubernetes sizing

Cluster sizing recommendations vary and are dependant on a number of deployment factors. We've provided the following general sizing recommendations, but encourage you to explore more personalized options.

CPU: 8 Cores

Memory: 32GB Ram

Number of Servers: 3

AWS: m5.2xlarge

Azure: D8 v3

IIS sizing

Application storage requirements

If you are choosing to deploy Cinchy v5 on IIS, then you need to ensure that your VM disks have enough application storage to run your clusters.

Object storage requirements

If you are using Terraform for your Kubernetes deployment, you will need to set up a new S3 compatible bucket to manually to store your state file. You will also need a bucket for Connections, to store error files created during data syncs.

Example Terraform Bucket: cinchy-terraform-state

Example Connection Bucket: cinchy-connections-cinchy-nonprod

S3 provides unlimited scalability and it charges only for what you use/how much you store on it, so there are no sizing definitions.

Contact support

If at any point you require help or clarification on this documentation or your Cinchy platform, you can reach out to our Support team:

• Via email: support@cinchy.com

• Via phone: 1-888-792-6051

• Through the support portal:

Overview

Becoming comfortable with the Cinchy terms and verbiage is an essential step to broaden your understanding of the platform and data collaboration as a whole. We've created this handy guide featuring to help you better understand the words and phrases you may see appear throughout this wiki space.

B

Batch Sync

A Batch Sync is one of the two types of Data Syncs that you can perform using Cinchy. Batch syncs work by processing a group or a ‘batch’ of data all together rather than each piece of data individually. When the data sync is triggered it will compare the contents of the source to the target. The Cinchy Worker will decide if data needs to be added, deleted or updated. Batch sync can either be run as a one-time data load operation, or it can be scheduled to run periodically using an external Enterprise Scheduler

C

Cinchy Administrator

The Cinchy Administrator is a user or user group with special Entitlements within the Cinchy platform. An administrator can be either an End-User or a Builder.

A Builder Admin can:

• Modify all table data (including system tables), all schema, and all data controls

  • This includes setting up and configuring users, assigning them to groups, and assigning which users have builder access

• View all tables (including system tables) and queries in the platform

A Non-Builder Admin can:

• View all tables (including system tables) and queries in the platform

• Modify data controls for tables

Cinchy Builder

“Cinchy Builders” use the Cinchy platform to build an unlimited number of business capabilities and use-cases.

The “Cinchy Builder” has access to perform the following capabilities:

• Change Table Schema (use Cinchy’s “Design Table” functionality)

• Grant access (use Cinchy’s “Design Controls” functionality)

• Edit Cinchy Data in Cinchy System Tables

• Create, Save, and Share Cinchy Queries

• Perform specific Cinchy Queries on the Cinchy data network

• Import/export packaged business capabilities (such as deployment packages)

• Build Cinchy Experiences

• Perform integration with Cinchy (Cinchy Command Line Interface [CLI] operations)

• Create and Deliver an unlimited number of Customer Use Cases within Cinchy

• A builder can be part of the Administrators group

Cinchy CLI

The Cinchy Command Line Interface (CLI) is a tool that offers utilities for syncing data in and out of Cinchy via various commands.

CinchyDXD

CinchyDXD is a downloadable utility used to move Data Experiences (DX) from one environment to another. This includes any and all objects and components that have been built for, or are required in, support of the Data Experience.

Cinchy ID

Cinchy ID is a unique identifier assigned automatically to all records within a normal table. The Cinchy ID is associated with the record permanently and is never reassigned even if the record is deleted.

Cinchy Query Language (CQL)

Cinchy Query Language (CQL) is the data collaboration version of common query languages such as SQL. With a robust set of functions and commands, you can use CQL to retrieve and manage/modify data and metadata from tables in your network. While data can reside across many tables, a query can isolate it to a single output, making the possibilities of CQL endlessly powerful.

Cinchy Query Language can be used in many ways, including but not limited to:

• ​Building queries through the query editor that can return, insert, delete, and otherwise manage your data.

• ​Creating, altering or dropping views for tables​

• ​Creating, altering or dropping indexes​

Cinchy Upgrade Utility

The Cinchy Upgrade Utility is an easy to use tool that can help you deploy important changes and upgrades to your Cinchy environment when upgrading versions. Note that not every major or minor release version will require the Utility to be run, however this will be clearly noted in the release notes and upgrade guide.

Cinchy End-User

The “End-Users” of the Cinchy platform are those that apply the functionalities created by the “Cinchy Builders” to their business objectives. This can be employees, customers, partners, or systems. Cinchy has two types of end-user: direct and indirect.

• Direct Users log into Cinchy via the data browser

• Indirect Users (also commonly referred to as "external users") view/edit data via a third-party application/page that connects to Cinchy via API

Cinchy End-Users are able to:

• Create and save personal queries. Unlike traditional saved queries made by builders, personal saved queries can't be shared and aren't auto exposed as APIs.

• Use Tables, Saved Queries, and Experiences created by “Builders"

• Track version history for the full lifecycle of data

• Bookmark and manage data

• Access data through application experiences

• An end-user can be part of the Administrators group

Connections Experience

The Connections Experience is an integral part of Cinchy data collaboration. Serving as the front-end UI for performing Data Syncs, the Connections Experience can be accessed natively in your Cinchy browser to create, configure, and manage the data being synced in and out of the platform. The user friendly interface makes synchronizing your data across a range of apps easy.

D

Data Browser

The Data Browser is the homepage of your Cinchy platform. From here you can view any Tables, Queries, or Experiences that you have access to. You can also use it to search and review any bookmarks that you have saved.

Data Collaboration

Data collaboration entails the collection, exchange and use of data from different origins within an organization. This interaction creates data products with data owners acting as experts for their specific data domains. The interaction of these various data set domains also results in dramatically informed insights that are solely driven by the interactions of the data itself.

Data Destination(s)

When setting up a Data Sync, you must define which destination to push data to. Cinchy maintains a robust list of Data Destination connectors and is always working on adding more into the Connections Experience.

Data Source(s)

When setting up a Data Sync, you must define which source to pull data from. Cinchy maintains a robust list of Data Source connectors and is always working on adding more into the Connections Experience.

Data Synchronizations

Data Synchronizations ("Data Syncs") are a powerful and important aspect of the Cinchy platform. Using a Data Sync allows you to bidirectional push and pull data between various Data Sources and Data Destinations, including Salesforce, Snowflake, REST APIs, and more. Built with keeping the core tenants of data collaboration in mind, Data Syncs adhere to your set Entitlements and can be configured using the intuitive Connections Experience to enable a myriad of use-cases for your business.

E

Entitlements

Entitlements refers to the set of permissions that you are granted for any piece of data. Cinchy allows entitlements to be set at a granular level, meaning you can give individual users or user groups access to things like:

• Viewing a data sync

• Running a job

• Viewing a specific table row or column

• Editing a specific table cell

• Etc.

Entitlements can persist across the platform when using features such as link columns or the Network Map.

Experiences

Rather than traditional code-centric applications which creates data silos, you can build metadata driven application experiences directly on the Cinchy platform. These look and feel like regular applications, but persists its data on the data network autonomously, rather than managing its own persistence. These experiences automatically adapt as your data evolves. An Org Chart is an example of a metadata driven Experience you can create using the power of data collaboration on Cinchy.

L

Listener configuration

Real-time data syncs need to set up a Cinchy listener configuration for event stream sources so that it knows what data to pull/push through your sync. As of 5.7, this can now be done under the Sources section in the Listener tab. Each stream source has different variables and parameters that you can use to refine your sync. The Listener only needs to be configured for real-time syncs, not for batch syncs.

M

MDQE

MDQE, which stands for Metadata Quality Exceptions, can send out notifications based on a set of rules and the “exceptions” that break them. This powerful tool can be used to send notifications for exceptions such as:

• Healthchecks returning a critical status

• Upcoming Project Due Dates/Timelines

• Client Risk Ratings reaching a high threshold

• Tracking Ticket Urgency or Status markers

• Unfulfilled and Pending Tasks/Deliverables

• Etc.

MDQE monitors for specific changes in data, and then pushes out notifications when that change occurs.

Meta Forms

The Meta-Forms experience is a combination of Angular code packaged as an App Experience and a Data Model packaged as a Data Experience that enables users to interact with Cinchy data in a User-Friendly manner.

It lives on top of the data collaboration platform, allowing builders to create forms with custom configurations and users to access this data outside of the tabular view that's native to Cinchy.

N

Network Map

Cinchy comes out of the box with a system applet called Network Map, which is a visualization of your data on the platform and how everything interconnects; it's another way to view and navigate the data you have access to within Cinchy.

Each node represents a table you have access to within Cinchy, and each edge is one link between two tables. The size of the table is determined by the number of links referencing that table. The timeline on the bottom allows you to check out your data network at a point in the past and look at the evolution of your network.

It uses your Entitlements for viewable tables and linked columns.

Q

Queries/Saved Queries

Queries are the fundamental calculations that allow data to be pushed, pulled, and manipulated across a range of Cinchy features. A query can be used to call data, change data, delete data, or whatever functionality your use case requires. Cinchy comes out of the box with a native Query Builder and works with our proprietary Cinchy Query Language.

A Saved Query is merely a query that has been saved for reuse. These can be accessed via the Saved Queries table in Cinchy, and you have access to view or run them based on your entitlements.

R

Real Time Sync

A Real-Time Sync is one of the two types of Data Syncs that you can perform using Cinchy. In real-time syncs, the Cinchy Listener picks up changes in the source immediately as they occur. These syncs don't need to be manually triggered or scheduled using an external scheduler. Setting up a real-time sync does require an extra step of defining a listener configuration to execute properly.

General Kubernetes deployment prerequisites

Before deploying Cinchy v5 on Kubernetes, you must follow the steps listed below.

Download your tools

Install the following tools on the machine where the deployment will run:

• Terraform

• Kubectl (v1.23.0+)

• .NET Core 6.0.x

• Bash (You can also use Git Bash on Windows)

Create your domains

All your Cinchy environments will need a domain for each of the following:

• ArgoCD

• OpenSearch

• Grafana

Do this through your specific domain registrar. For example, GoDaddy or Google Domains.

SSL certs

You must have valid SSL Certs ready when you deploy Cinchy v5. Cinchy recommends using a wildcard certificate if ArgoCD will be exposed via a subdomain. Without the wildcard certificate, you must create a port forward using kubectl on demand to access ArgoCD's portal.

You also have the option to use Self-Signed Certs in Kubernetes deployments. Find more information here.

Secrets management

Although optional, Cinchy strongly recommends secret management for storing and accessing secrets that you use in the deployment process. Cinchy currently supports:

• Amazon Secrets Manager

• Azure Key Vault

Single sign-on

If you would like to set up single sign-on for use in your Cinchy v5 environments, please review the SSO integration page.

Docker images

You can use Cinchy Docker images or your own. If you would like to use Cinchy images, please follow the section below to access them.

Access Cinchy Docker images

You will pull Docker images from Cinchy's AWS Elastic Container Registry (ECR).

To gain access to Cinchy's Docker images, you need login credentials to the ECR. Contact Cinchy Support for access.

Create your repositories

You must create the following four Git repositories. You can use any source control platform that supports Git, such as Gitlab, Azure DevOps, or GitHub.

• cinchy.terraform: Contains all Terraform configurations.

• cinchy.argocd: Contains all ArgoCD configurations.

• cinchy.kubernetes: Contains cluster and application component deployment manifests.

• cinchy.devops.automations: Contains the single configuration file and binary utility that maintains the contents of the above three repositories.

Access to Cinchy artifacts

You will need to access and download the Cinchy artifacts before deployment.

To access the Kubernetes artifacts:

1. Access the Cinchy Releases table. Please contact Cinchy Support if you don't have the access credentials necessary.

2. Navigate to the release you wish to deploy.

3. Download the .zip file(s) listed under the Kubernetes Artifacts column.

4. Check the contents of each of the directories into their respective repository.

Kubernetes Azure requirements

If you are deploying Cinchy v5 on Azure, you require the following:

Terraform requirements

• A resource group that will contain the Azure Blob Storage with the terraform state.

• A storage account and container (Azure Blob Storage) for persisting terraform state.

• Install the Azure CLI on the deployment machine. It must be set to the correct profile/login

The deployment template has two options available:

• Use an existing resource group.

• Creating a new one.

Existing resource group

If you prefer an existing resource group, you must provision the following before the deployment:

• The resource group.

• A VNet within the resource group.

• A single subnet. It's important that the address range be enough for all executing processes within the cluster, such as a CIDR ending with /22 to provide a range of 1024 IPs.

New resource group

• If you prefer a new resource group, all resources will be automatically provisioned.

• The quota limit of the Total Regional vCPUs and the Standard DSv3 Family vCPUs (or equivalent) must offer enough availability for the required number of vCPUs (minimum of 24).

• An AAD user account to connect to Azure, which has the necessary privileges to create resources in any existing resource groups and the ability to create a resource group (if required).

Kubernetes AWS requirements

If you are deploying Cinchy v5 on AWS, you require the following:

Terraform requirements:

• An S3 bucket that will contain the terraform state.

• Install the AWS CLI on the deployment machine. It must be set to the correct profile/login

The template has two options available:

• Use an existing VPC.

• Create a new one.

Existing VPC

• If you prefer an existing VPC, you must provision the following before the deployment:

  • The VPC. It's important that the address range be enough for all executing processes within the cluster, such as a CIDR ending with /21 to provide a range of 2048 IPs.

  • 3 Subnets (one per AZ). It's important that the address range be enough for all executing processes within the cluster, such as a CIDR ending with /23 to provide a range of 512 IPs.

  • If the subnets are private, a NAT Gateway is required to enable node group registration with the EKS cluster.

New VPC

• If you prefer a new VPC, all resources will be automatically provisioned.

• The limit of the Running On-Demand All Standard vCPUs must offer enough availability for the required number of vCPUs (minimum of 24).

• An IAM user account to connect to AWS which has the necessary privileges to create resources in any existing VPC and the ability to create a VPC (if required).

• You must import the SSL certificate into AWS Certificate Manager (or a new certificate can be requested via AWS Certificate Manager).

• You must import the SSL certificate into AWS Certificate Manager, or a new certificate can be requested via AWS Certificate Manager.

• If you are importing it, you will need the PEM-encoded certificate body and private key. You can find this, you can get the PEM file from your chosen domain provider (GoDaddy, Google, etc.) Read more on this here.

IIS deployment prerequisites

Before deploying Cinchy v5 on IIS, you require the following:

Access the artifacts

You need to access and download the Cinchy binary before deployment:

• Access the Cinchy Releases table. Please contact Cinchy Support if you don't have the access credentials necessary.

• Navigate to the release you wish to deploy

• Download the files listed under the Component Artifacts column. This should include zip files for:

  • Cinchy Platform

  • Cinchy Connections

  • Cinchy Event Listener

  • Cinchy Maintenance CLI and CLI (optional)

  • Cinchy Meta-Forms (optional)

General requirements

1. An instance of SQL Server 2017+

2. A Windows Server 2012+ machine with IIS 7.5+ installed

3. Install .net core Hosting bundle Version 6.0

   • Specifically, install: ASP.NET Core/.NET Core Runtime & Hosting Bundle

System requirements

Minimum web server hardware recommendations

• 2 × 2 GHz Processor

• 8 GB RAM

• 4 GB Hard Disk storage available

Minimum database server hardware recommendations

• 4 × 2 GHz Processor

• 12 GB RAM

• Hard disk storage dependent upon use case. Note that Cinchy maintains historical versions of data and performs soft deletes which will add to the storage requirements.

Clustering

Clustering considerations are applicable to both the Web and Database tiers in the Cinchy deployment architecture.

The web tier can be clustered by introducing a load balancer and scaling web server instances horizontally. Each node within Cinchy uses an in-memory cache of metadata information, and expiration of cached elements is triggered upon data changes that would impact that metadata. Data changes processed by one node wouldn't be known to other nodes without establishing connectivity between them. The nodes must be able to communicate over either HTTP or HTTPS through an IP based binding on the IIS server that allows the broadcast of cache expiration messages. The port used for this communication is different from the standard port that's used by the application when a domain name is involved. Often for customers this means that a firewall port must be opened on these servers.

The database tier relies on standard MS SQL Server failover clustering capabilities.

Scaling Cconsiderations

The web application oversees all interactions with Cinchy be it through the UI or connectivity from an application. It interprets/routes incoming requests, handles serialization/deserialization of data, data validation, enforcement of access controls, and the query engine to transform Cinchy queries into the physical representation for the database. The memory footprint for the application is low, as caching is limited to metadata, but CPU use grows with request volume and complexity(For example, insert/update operations are more complex than select operations). As the user population grows or request volume increases, there may be a need to add nodes.

The database tier relies on a persistence platform that scales vertically. As the user population grows and request volume increases, the system may require additional CPU / Memory. Cinchy recommends you start off in an environment that allows flexibility (such as a VM) until you can profile the real-world load and establish a configuration. On the storage side, Cinchy maintains historical versions of records when changes are made and performs soft deletes of data which will add to the storage requirements. The volume of updates occurring to records should be considered when estimating the storage size.

Backups

Outside of log files there is no other data generated & stored on the web servers by the application, which means backups are centered around the database. Since the underlying persistence platform is a MS SQL Server, this relies on standard procedures for this platform.

Can I get a record count from a delimited file before running the CLI?

You can use PowerShell to count the lines in a delimited file and based on the result decide if you will run the CLI.

$row_count=(get-content sample_150k.csv).length
write-host $row_count

If ($row_count -lt 50000)
{
     exit
}
else {
      Write-host "run CLI"
}

If you run a CLI without performing the sync, there is currently no way for you to find out how many records will be inserted/updated/deleted.

Can I restore my deleted data?

If the record is still in the table, but has been overwritten by mistake, access your Collaboration Log for the row, and restore back to the correct version.

If your row has been deleted by mistake, access your Recycling Bin, locate the row and restore it.

The only way to truly delete data on the platform is through Data Erasure and Data Compression.

Can I send multiple comma-delimited values to a query parameter? [234,233,365 to be used in WHERE [Id] IN (@param)]

For example: 4,10,15 to be used in WHERE [Id] IN (@param)

This can be done by using parameters in {}, such as {0},{1}.

These will be replaced with the exact text when running the query.

For example: ​ SELECT * FROM [HR].[Employees] WHERE [Deleted] IS NULL AND [Employee ID] IN ({0}) (Image 1).

Cinchy dates are being saved as a 1900-01-01 when updating using a variable

When updating a date field using a variable, and no value is entered for that variable, the date field will be 1900-01-01. To avoid this, use a case statement to replace the empty string with NULL, as shown in the following example:

UPDATE E
SET E.[Date Hired]=CASE WHEN @dhired<>'' THEN @dhired ELSE NULL END
FROM [HR].[Employees] E
WHERE E.[Deleted] IS NULL AND [Employee ID]=@empid

Does a row filter restrict access for a Cinchy administrator?

Cinchy Administrators have access to view/edit/approve all data in the platform, so there's no way to restrict access for Cinchy administrators.

A workaround is to create a separate "administrators" group which has edit access to all Cinchy system tables, and just leave the "admin" user account or super admins as "Cinchy administrators."

How can I automatically check if a CLI data sync was successful or failed?

You can check if a data sync was successful by its exit code. Below is sample code in PowerShell to check for the exit code and what they mean.

Invoke-Expression $CLICommand
switch ($LASTEXITCODE) {
  0 { Write-Host "Completed without errors" }
  1 { Write-Host "Execution failed" }
  2 { Write-Host "Completed with validation errors" }
}

From the command prompt the following will also return the error code:

echo %ErrorLevel%

How can I check for platform errors?

In Version 4:

First check <base URL>/elmah, which stores web-related induced errors.

Then check the logs, which can be accessed from <base URL>/admin/index.

Cinchy logs will contain all exceptions thrown by the Cinchy Web application. This includes failed queries, stack overflows and much more.

CinchySSO logs will contain IDP errors.

In Version 5: Errors and Logs can be found through the OpenSearch Dashboard.

How can I enter a new line into a field in Manage Data?

You can add line breaks in a cell on the UI, the same way as in Excel, by typing Alt+Enter. If you use the expanded row heights option, or manually expand the row, it will show the line breaks.

How can I prevent wrong data loading in from external applications?

The best way to load data from external sources into Cinchy, is by using a data sync.

You can do the following to preview your changes:

• Create staging tables to validate the data first.

• Use formatting rules in Cinchy, to highlight data that's not valid.

• Configure a CLI using a Cinchy Query source to move the data from the staging tables to the permanent tables.

How can I see who has modified my data?

Right click on the row you want additional information and select the Collaboration Log.

You can also add the "Modified By" and "Modified" columns into the current view/to your query if you want to see it for multiple rows at once.

How do I create a Cinchy user with a set password?

One Time setup:

1. Open the Users table

2. For the password of this user, copy the admin user password and paste it into the Password field of the new user.

3. Set the Password Expiration Timestamp to today.

4. In an Incognito browser, navigate to the Cinchy website.

5. Sign in as the new user with the admin user password.

6. Cinchy will ask you to change the password for the new user, change it to a default password you will give out every time you create an account.

7. In the original session window, refresh the Users table and remove the Password Expiration Timestamp for the new user.

Each time, for new users:

1. Open the Users table.

2. Create the new user, for example sandip.

3. For the password of this user, copy the the new user password and paste it into the Password field of sandip.

4. Set the Password Expiration Timestamp to today.

5. Give the user their username and friendly password created in step 7 above. They will be asked to change their password on first sign in.

How do I get the change history through CQL?

You write the query for the records for which you want the change history, including system columns like [Version], [Created], and the columns for which you like to see the changes.

You can add an ORDER BY [Version] (either ASC or DESC)

Then you change the query return type to "Query Results (Including Version History )"

The following query will show when the Cinchy instances were upgraded.

SELECT [Version], [Modified], [Model Version]
FROM [Cinchy].[Models]
WHERE [Deleted] IS NULL AND [Name]='Cinchy'
ORDER BY [Version] DESC

How do I insert, update, and delete links in a multi-select field using CQL?

Removing and updating a multi-select a link, is the same as setting the link field. The field needs to be updated with the list of values.

The value is a concatenated string of '[Cinchy Id],[Version],[Cinchy Id],[Version],[Cinchy Id],[Version]' from the lookup values

UPDATE T
SET T.[Multi-Link Field Name] = '1,1,2,1,3,1'
FROM [Domain].[Table Name] T
WHERE T.[Deleted] IS NULL AND ...

In this example it would set [Multi-Link Field Name] to values with [Cinchy Id] 1, 2, and 3. The version after each Cinchy Id should be 1.

How do I know which version of Cinchy I am running?

Navigate to <baseURL>/healthcheck

(ex. if your current URL is https://cinchy.mycompany.com/Tables/123?viewId=0 then you would navigate to https://cinchy.mycompany.com/healthcheck)

The response looks this:

{
  "component": "Cinchy",
  "version": "4.14.0.0",
  "ipAddress": ["172.31.14.171", "172.19.64.1"],
  "systemTime": "2020-06-18T19:43:54.1692859Z",
  "status": "Green",
  "healthChecks": [
    {
      "name": "Database Connectivity",
      "description": "Validates that the application can connect to the database",
      "status": "Green"
    }
  ]
}

In this case your Cinchy version is 4.14.0.0

If you would like to use the health check link for monitoring of the Cinchy application you can add "return503OnFailure=true" to the URL

How do I map a parameter's value to one of my target columns?

Use the model loader to load it back in the system (/apps/modelloader).

You create a calculated column in the source and give it the value of the parameter.

For each table, export and import the data via the UI.

<Parameters>
    <Parameter name="snapshotDate" />
</Parameters>
...
<Schema>
...
    <CalculatedColumn name="Snapshot Date" formula="@snapshotDate" dataType="Date" />
<Schema>

Then map the calculated source column to the target. The order of the columns in the source is important. If your source is a file, put the calculated columns at the end in the source, after all the actual columns in the file.

How do I parse a pipe delimited file using the CLI?

Set the delimiter to |.

<DelimitedDataSource delimiter="|" textQualifier="&quot;"  headerRowsToIgnore="2" path="@filePath" encoding="UTF8">

How do I remove the leading 0 from an incoming field using the CLI?

This can be done by using Transformations in the sync configuration of a column. Here is an example:

<Column name="Value 2" dataType="Text">
  <Transformations>
    <StringReplacement pattern="^0*" replacement="" />
  </Transformations>
</Column>

The pattern contains a regular expression:

^ - anchor for the beginning of the string

0 - the string to replace

* - quantifier to be applied to 0 or more occurrences

How do I clone a Cinchy table?

1. Export the Model to XML from the Design Table info tab

2. Open the exported model in an editor and change the name of the model

3. Change the name of the table

4. remove the guids from the table in the model and save the file

5. Use the modelloader at <cinchy base URL>/apps/modelloader to upload the modified model

6. Export the data from the Manage Data screen of the initial table and import it in the new table

How do I clone a domain?

If you just have a group of tables, see the instructions below. If you have tables, queries, you want to port the permissions, etc. you can use this: Data Experience Deployment - Cinchy Platform Documentation

Table only instructions:

1. Create a dummy Data Experience and add all your tables from your domain to it (Image 2).

2. Hit the following endpoint with the GUID in your row:

<CinchyURL>/api/createdxversion?guid=<GUID>

I am unable to use COALESCE with a link column in a calculated column

If [Person 1] and [Person 2] are Link columns and [Member] is a Text column, a calculated column with the following expression will fail to save:

COALESCE([Person 1],[Person 2],[Member])

Please cast the link columns to VARCHAR:

COALESCE(CAST([Person 1] AS VARCHAR(50)),CAST([Person 2] AS VARCHAR(50)),[Member])

I can't disable Change Approvals

This is caused by records in Draft status. To retrieve these records, run a query with return type Query Results (Including Draft Data).

SELECT T.*
FROM [Your Domain].[Your Table] T
WHERE T.[Approval State] <> 'Approved'

After approving these records, you will be able to disable change approval.

I can't find the [Cinchy].[Table Access Control] table

The [Cinchy].[Table Access Control] table doesn't show in the Market Place, but you can query for the data in the table.

SELECT *
FROM [Cinchy].[Table Access Control]
WHERE [Deleted] IS NULL AND [Table]='HR.Employees'

I can't find the column I want to link to even though the column is present in the table

In this example it would set [Multi-Link Field Name] to values with [Cinchy Id] 1, 2, and 3. The version after each Cinchy Id should be 1."

Columns don't "Allow Linking" by default. Check the properties of the column in the original table and make sure that in “Show Advanced” the “Allow Linking” checkmark box is selected. If you don't have Design Table access to that table, you will need to ask someone who does to do it.

I have access to a table but I can't see any rows

You may not be able to see any rows because of the following reasons:

• View Filter

• Data access controls

• Error with the View or Table

View Filter

Check the All Data view and see if there is data there, if that's the case but a particular view has no rows, there could be a filter on the view. For example, if there is a "Due Soon" or "My Actions" view, it could just be that there are no records assigned to you that require action.

Data Access Controls

Access controls set on the table could cause you to have access to 0 records. Since you are able to set row level filters in Cinchy, it may be the case that the permissions of the table hasn't changed, but the data has changed such that you no longer have permission or vice versa.

Error

There may be an error on the view. If the bottom of the page doesn't show 0 records then there may be an error on the page (Image 3).

Is it possible to correct/replace a table or column's GUID?

It can be done. It's unlikely that the GUID you want to change to is already allocated, but you should still check. Filter the [Cinchy].[Table Columns] for the new GUID. You shouldn't find it. Then replace it in two places:

• the JSON field in [Cinchy].[Tables] - replace it in the column definition

• the GUID field in [Cinchy].[Table Columns]

To replace the table GUID, replace it in the JSON in [Cinchy].[Tables] and in the GUID field in [Cinchy].[Tables].

When you are done, restart the Cinchy UI. ​

My Insert/Update statement is making multiple changes instead of just one

A query like the following will cause multiple inserts instead of one if your result type is set to Query Results instead of # of Rows Affected.

INSERT INTO [Customer].[Tickets] ( [Ticket Id], [Subject] )
VALUES ( 1900, 'This is a Test' );
SELECT [Cinchy Id],
       [Ticket Id],
       [Subject]
FROM [Customer].[Tickets]
WHERE [Deleted] IS NULL

The same applies to UPDATE statements.

If you need to perform inserts and updates in a query and want to return data at the end, another option is to use the "Single value (First Column of First Row)" return type, which will only be able to return a single value.

My query parameter isn't working

When I pass a value to the following query, the result is empty.

DECLARE @nbdays AS INT;
SELECT @nbdays;

The query works without the DECLARE statement. When the DECLARE statement is present, the input variable is ignored, and needs to be SET. To still get the variable from the input, a second variable is needed.

DECLARE @nbdays AS INT;
SET @nbdays = @inputDays;
SELECT @nbdays;

Null values aren't updating correctly in Salesforce using the data sync

When performing a data sync with a Salesforce target, you need to replace nulls with '#N/A' in the source. You can use ISNULL([Column],'#N/A') in the source query. The following is a link to the Salesforce documentation related to this topic:

https://help.salesforce.com/articleView?id=000328822&language=en_US&type=1&mode=1

Passing Parameters to a query called With Exec

Declare and set the parameters before invoking the query:

DECLARE @dep AS VARCHAR ( 500 );
SET @dep = 'Accounting';
EXEC [HR].[Employees and Departments]

[HR].[Employees and Departments] is:

SELECT [Employee ID],
       [Full Name],
       [Date Hired],
       [Department]
FROM [HR].[Employees]
WHERE [Deleted] IS NULL
      AND [Department] = @dep

Some of the columns have been rearranged under the default "All Data" view

The default All Data view displays the columns in the same order as in Design Table. But you can create a view and change the columns displayed and their order.

The multi-select option on the Link Column is disabled

Once link column is added to a table and saved, the multi-select checkbox should be disabled. If you need to change the option, you need to rename the column and create a new link column with the correct option.

What permissions are needed for a user to be able to create and edit views?

The user needs to have "Design Table" permissions granted for the table where they will create or edit views and also needs to have the "Can Design Tables" checked in the [Cinchy].[Users] table.

New Capabilities

Connections

Test connections

We made it simpler to debug invalid credentials in data syncs by adding a "Test Connection" button to the UI for the following sources and destinations:

Selecting this button will validate whether your username/password/connection string/etc. are able to connect to your source or destination. If successful, a "Connection Succeeded" popup will appear. If unsuccessful, a "Connection Failed" message will appear, along with the ability to review the associated troubleshooting logs. With this change, you are able to debug access-related data syncs at a more granular level.

Listener config integration

As we continue to enhance our Connections Experience offerings, you can now configure your listener for real-time syncs directly in the UI without having to navigate to a separate table. For any event triggered sync source, (CDC, REST API, Kafka Topic, MongoDB Event, Polling Event, Salesforce Platform Event, and Salesforce Push Topic), there is now the option to input your configurations directly from the Source tab in the Connections Experience. Any configuration you populate via the UI will be automatically reflected back into the Listener Config table of your platform.

You are able to set the:

• Topic JSON

• Connections Attributes

• Auto Offset Reset

• Listener Status (Enabled/Disabled)

Information on the parameters and configurations for the above settings can be found here and here.

For ease of use, we also added help tips to the UI, as well as examples where necessary.

New Source: Oracle Polling Connector

We added Oracle as a new database type for Polling Events in Connections. Data Polling is a source option first featured in Cinchy v5.4 which uses the Cinchy Event Listener to continuously monitor and sync data entries from your Oracle, SQL Server, or DB2 server into your Cinchy table. This capability makes data polling a much easier, effective, and streamlined process and avoids implementing the complex orchestration logic that was previous necessary.

Source filter additions

For REST API, SOAP 1.2, Kafka Topic, Platform Event, and Parquet sources, we added a new "Conditional" option for source filters in the Connections UI. Similarly to how the "Conditional Changed Record Behaviour" capability, works, once selected you will be able to define the conditions upon which data is pulled into your source via the filter. After data is pulled from the source, new conditional UI filters down the set of returned records to ones that match the defined conditions.

Cinchy Secrets table

The Cinchy platform now comes with a new way to store secrets — the Cinchy Secrets Table. Adhering to Cinchy’s Universal Access Controls, you can use this table as a key vault (such as Azure Key Vault or AWS Secrets Manager) to store sensitive data only accessible to the users or user groups that you give access to.

You can use secrets stored in this table anywhere a regular variable can go when configuring data syncs, including but not limited to:

• As part of a connection string;

• Within a REST Header, URL, or Body;

• As an Access Key ID.

You can also use it in a Listener Configuration.

Additionally, we've implemented a new API endpoint for the retrieval of your secrets. Using the below endpoint, fill in your <base-url>, <secret-name>, and the <domain-name> to retrieve the referenced secret.

This endpoint works with Cinchy’s Personal Access Token capability, as well as Access Tokens retrieved from your IDP.

Blank Example:

<base-url>/api/v1.0/secrets-manager/secret?secretName=<secret-name>&domain=<domain-name>

Populated Example:

Cinchy.net/api/v1.0/secrets-manager/secret?secretName=<ExampleSecret>&domain=<Sandbox>

The API will return an object in the below format:

{
    "secretValue": "password123"
}

Polling listener optimization

To improve your Connections experience, we made various optimizations to our Polling Event Listener.

• We added a new configurable property, DataPollingConcurrencyIndex, to the Data Polling Event Listener. This property allows only a certain number of threads to run queries against the source database, which works to reduce the load against the database. The default number of threads is set to 12. To configure this property, navigate to your appSettings.json deployment file > "DataPollingConcurrencyIndex": <numberOfThreads>

• We added a new configurable property, QueueWriteConcurrencyIndex, to the Data Polling Event Listener. This property allows only a certain number of threads to be concurrently sending messages to the queue. This works to provide a more consistent batching by the worker and reduce your batching errors. run queries against the source database, which works to reduce the load against the database. The default number of threads is set to 12. To configure this property, navigate to your appSettings.json deployment file > "QueueWriteConcurrencyIndex": <numberOfThreads>. Note that this index is shared across all listener configs, meaning that if it's set to 1 only one listener config will be pushing the messages to the queue at a single moment in time.

• We added a new mandatory property, CursorConfiguration.CursorColumnDataType, to the Listener Topic for the Data Polling Event. This change was made in tandem with an update that ensure that the database query always moved the offset, regardless of if the query returned the records or not—this helps to ensure that the performance of the source database isn't being weighed down by constantly running heavy queries on a wide range of records when the queries returned no data. This value of this mandatory property must match the column type of the source database system for proper casting of parameters.

• We added a new configurable property, CursorConfiguration.Distinct, to the Listener Topic for the Data Polling Event. This property is a true/false Boolean type that, when set to true, applies a distinct clause on your query to avoid any duplicate records.

// App Settings JSON Example
// Example of the new configurable propeties: DataPollingConcurrencyIndex (set to "1") and QueueWriteConcurrencyIndex (set to "1")
"AppSettings": {
    "GetNewListenerConfigsInterval": "",
    "StateFileWriteDelaySeconds": "",
    "KafkaClientConfig": {
      "BootstrapServers": ""
    },
    "KafkaRealtimeDatasyncTopic": "",
    "KafkaJobCancellationTopic": "",
    "DataPollingConcurrencyIndex":  1,
    "QueueWriteConcurrencyIndex":  1
  }

// Listener Config Topic Example
// Example of the new mandatory CursorColumnDataType property, which below is set to "int", and "Distinct", below set to "true".
{
   "CursorConfiguration": {
       "FromClause": "",
       "CursorColumn": "",
       "BatchSize": "",
       "FilterCondition": "",
       "Columns": [],
            "Distinct": "true"
            "CursorColumnDataType" : "int"
   },
        "Delay": ""
}

Enhancements

Connections

We made various enhancements to the Connections Experience which should help to simplify and streamline your ability to create and maintain data synchronizations across the platform. Examples of these changes can be found in our Data Sync documentation.

Radio buttons for selection

• Replaced drop-down menus with radio buttons for the following options:

  • Sync Strategy

  • Source Schema Data Types

  • Source Schema "Add Column"

Improved visibility

• Expanded the width and height of source, destination, and connections drop-down menus to ensure visibility, even on screens with varying sizes.

Streamlined file-based source fields

• Streamlined the organization of file-based source fields for greater efficiency.

Simplified options

• Eliminated the following fields for a more focused interface:

  • Source > Cinchy Table > Model

  • Info > Version

• The API Response Format field has been removed from the REST Source configuration. This change reflects that the only supported response format is JSON.

Refined order of operations

• Reorganized the process steps, moving the "Permissions" step within the "Info" tab.

Clearer terminology

• Adjusted terminology for clarity and consistency:

  • Renamed Sync Behaviour tab to Sync Actions.

  • Replaced Parameters with Variables.

  • Changed "Sync Pattern" to Sync Strategy in the Sync Actions tab.

  • Updated Column Mappings to Mappings in the Destination tab.

  • Substituted Access Token with API Key in the Copper Source, aligning with Copper's documentation language.

Enhanced guidance

• Included descriptive explanations in various sections, such as Mapping, Schema, and Sync Behaviour, to provide comprehensive guidance during data sync configuration.

Unified language

• Standardized language used in file-based connectors across all Sources.

Improved clarity

• Added clarifying text throughout the interface for smoother navigation and configuration, fostering a more user-friendly experience.

Organizational enhancements

• Grouped Sources by type, distinguishing between Batch and Event categories.

• Implemented alphabetical sorting for improved accessibility and ease of locating connections.

Simplified Destination setup

We've streamlined the destination setup process for data syncs. When selecting a Source other than Cinchy, the destination is now automatically set as Cinchy Table. This enhancement speeds up the creation of data syncs.

Unique identifiers for saved connections

To assist sharing and collaboration on connections, we've introduced unique URLs for all saved connections. Each connection now possesses a unique URL that can be shared with other platform users. This URL links directly to the saved configuration.

Enhanced Load Metadata process

We've made significant improvements to the Load Metadata sources and destinations, enhancing user experience:

• The Load Metadata modal no longer appears automatically when selecting a relevant source or destination.

• The availability of the Load Metadata button is conditional on filling out parameters in the Connection section.

• Clicking the Load Metadata button now directly takes you to metadata columns, skipping the interstitial modal.

• In the Schema section, all columns are now collapsed by default. Manually added columns maintain an expanded view.

Redesigned UI for Listener Configurations

For simpler real-time sync setups, the Cinchy Event Broker has a new Listener section. This section assists in creating topic JSON for listener configurations, eliminating the need to manually set up topic JSON in the Listener Config table. Refer to the Cinchy Broker Event source page for details on topic JSON fields.

Modal dismissal

We've introduced the ability to dismiss most modals using the Escape key. This enhancement provides a more convenient and user-friendly interaction experience.

Logging

Log outputs

To help simplify and streamline the Connections experience, you are now able to view the output for each job by clicking on the Output button located in the Jobs tab of the UI after you run a sync.

This links to the Execution Log table with a filter set for your specific sync, which can help you reach your execution related data quicker and easier than before.

Log full REST Target HTTP response

We now log the full REST Target HTTP response in the data sync Execution Errors table to provide you with more detailed information about your job. This replaces the original log that only contained the HTTP response status code.

MongoDB update

We continue to provide optimization updates to our Connections capabilities. v5.7 of the Cinchy platform has the following updates for the MongoDB Event Stream:

• A new configurable property, QueueWriteConcurrencyIndex, to the MongoDB Event Listener. This property allows only a certain number of threads to be concurrently sending messages to the queue. This works to provide a more consistent batching by the worker and reduce your batching errors. run queries against the source database, which works to reduce the load against the database. The default number of threads is set to 12. To configure this property, navigate to the appSettings.json > QueueWriteConcurrencyIndex: <numberOfThreads>. This index is shared across all listener configs, meaning that if it's set to 1 - only one listener config will be pushing the messages to the queue at a single moment in time.

• We also added a new optional property to the MongoDB Listener Topic, 'changeStreamSettings.batchsize’, that's a configurable way to set your own batch size on the MongoDB Change Stream Listener.

{
  "database": "",
  "collection": "",
  "changeStreamSettings": {
    "pipelineStages": [],
    "batchSize": "1000"
  }
}

Faster query performance for PostgreSQL multi-select column joins

We optimized PostgreSQL query performance when referencing multi-select columns.

Improved query performance using CASE statements

We improved query performance when using a CASE statement on a Link reference.

Meta-Forms

UI changes

• We consolidated all actions into a single menu for easier navigation.

• We moved Create new record into the single menu and renamed it to Create.

• We added an option to copy the record link (URL) to the clipboard.

• We changed Back to Table View to View Record in Table.

Forms action bar

To improve the user experience and make interacting with forms easier, we made the Forms action bar always visible when you scroll through a form.

URL sync with record selection

We updated the URL to accurately match the record currently displayed, when switched from the records dropdown menu.

Unsaved changes prompt in forms

You'll now get a prompt to save if you have unsaved changes in a form.

Required fields alert for child forms

We added a warning message in child forms when essential columns like "Child Form Link Field" or both "Child Form Parent ID" and "Child Form Link ID" are missing, as they're needed for proper functionality.

Platform

General security enhancements

We made several updates and enhancements to packages across Cinchy to improve our platform security.

Link column enhancements

We updated the dropdown menus for Link columns to display selected and deleted values at the top of the list so that you don't need to scroll through long lists just to find the ones you've selected.

IdentityServer4 to IdentityServer6 upgrade

We upgraded our IDP from IdentityServer4 to IdentityServer6 to ensure we're maintaining the highest standard of security for your platform.

Add Execute function to UDF extensions

We added execute, a new method for UDF extensions. This new query call returns a queryResult object that contains additional information about your result. For more information, see the Cinchy User Defined Functions page.

Expand platform support for DXD

We added additional system columns to extend the number of core Cinchy objects that can be managed through DXD 1.7 and higher.

The newly supported Cinchy objects are:

• Views (Data Browser)

• Listener Config

• Secrets

• Pre-install Scripts

• Post-install Scripts

• Webhooks

mTLS support

We implemented Istio mTLS support to ensure secure/TLS in-cluster communication of Cinchy components.

Bugs

Platform

• We fixed a bug in the Cinchy Upgrade Utility that was causing the use of the -c flag, which is meant to delete extra metadata created on the database, to instead run (or rerun) the entire upgrade process.

• We fixed a bug that was stripping query parameters from Relative URLs if they were being used as the Application URL of the applets. In the below screenshot, the bug would have stripped out the "q=1" parameter, leaving only an Absolute URL in lieu of a Relative one.

• We fixed an issue with the behaviour of cached calculated columns when using multi-select data types (Link, Choice, and Hierarchy) with Change Approval enabled. These data types should now work as expected.
• We resolved an issue that prevented view exports from reaching the maximum limit of 250,000 records.

Connections

• We fixed a bug where the UUID/ObjectId in a MongoDB Change Stream Sourced data sync wasn't being serialized into text format. If you have any MongoDB Stream Sourced syncs currently utilizing the UUID/ObjectId, you may need to adjust accordingly when referencing the columns with those data types.

// Previous UUID/ObjectIDs would have been serialized as the below:
{
  "_id": ObjectId('644054f5f88104157fa9428e'),
  "uuid": UUID('ca8a3df8-b029-43ed-a691-634f7f0605f6')
}

// They will now serialize into text format like this:
{
  "_id": "644054f5f88104157fa9428e",
  "uuid": "ca8a3df8-b029-43ed-a691-634f7f0605f6"
}

• We fixed a bug where setting a user’s time zone to UTC (Coordinated Universal Time) would result in no data being returned in any tables.

• We fixed a bug where the Sync GUID of Saved Queries transferred over via DXD would null out.

• We fixed a bug affecting the MongoDB Event Listener wherein the “auto offset reset” functionality would not work as anticipated when set to earliest.

• We fixed a bug where failed jobs would return errors for logs that haven't yet been created. Log files now correctly search for only the relevant logs for the failed job.

• We fixed an issue in the data configuration table where the IF field for the Delimited File > Conditional Calculated Column wasn't displaying correctly.

• We resolved an issue where using multiple parameters while configuring data syncs could result in parsing and execution errors.

• We fixed a bug preventing calculated columns from working in MongoDB targets for data syncs.

• We fixed a bug where users were prompted to restore unsaved changes for a new connection when no configuration changes to a data sync were made.

• We fixed a bug that was causing the platform to fail upon initializing when a System User had been added to any user group (such as the Connections or Admin groups).

• We fixed a bug where passing an encrypted value to a variable used in a field encrypted by the connections UI would cause the sync to fail. You can now use variables with either encrypted or plaintext values.

• We fixed a bug where using the "Delta" sync strategy led to duplicating existing records in some destinations before inserting the new rows of data.

Meta-Forms

• We fixed a bug where child record tables within a form would display data differently when exported to a PDF.

• We fixed an issue where the first load of an applet wouldn't render sections that require Cinchy data until you refreshed the page.

• We fixed an issue where raw HTML was being displayed instead of HTML hyperlinks.

• We fixed a bug that prevented a form from loading if you deleted an associated child form.

• We fixed an issue with the record dropdown search where inputs of more than 30 characters caused a failure to match.

Component diagram

The below diagram shows a high level overview of Cinchy's Infrastructure components when deploying on IIS.

Component overview

Certainly, presenting the information in a table can help make it easier to understand. Here's how you can structure it:

Before You Begin

Before starting with the ADFS configuration, make sure to have following information:

Having these details readily available will streamline the ADFS configuration process.

Configuration Steps in ADFS

1. Navigate to AD FS Management on your ADFS server.

2. Right-click on Relying Party Trusts and choose Add Relying Party Trust to open the Add Relying Party Trust Wizard.

3. In the wizard, select Claims Aware > Start > Select Data Source.

4. Select Enter Data About the Relying Part Manually > Next.

5. Fill in a Display Name under Specify Display Name.

6. Skip certificate configuration in Configure Certificates.

7. In Configure URL, select Enable support for the SAML 2.0 SSO Web SSO protocol.

8. Input your login URL as follows:

   Copy

   https://{your.cinchysso.url}/Saml2/Acs

9. Under Configure Identifiers, add an Identifier and press Next to complete the setup.

Set up Claim Issuance Policy

1. Right-click on the newly created Relying Party Trust (located by its Display Name) and select Edit Claim Issuance Policy.

2. Select Add Rule > Claim Rule > Send LDAP Attributes as Claims.

3. Input a Claim Rule Name.

4. In the Attribute Store, select Active Directory. Map the LDAP attributes to the corresponding outgoing claim types as shown in the table below:

1. Select Finish.

2. Select Edit Rule > View Rule Language. Copy the Claim URLs for later use in configuring your Cinchy appsettings.json. It should look like the following:

   Copy

   c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", Issuer == "AD AUTHORITY"]
     => issue(store = "Active Directory",
             types = ("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier",
                       "sub",
                       "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname",
                       "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname",
                       "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress",
                       "http://schemas.microsoft.com/ws/2008/06/identity/claims/role"),
             query = ";userPrincipalName,sAMAccountName,givenName,sn,mail,memberOf;{0}",
             param = c.Value);

3. Press OK to confirm and save.

Configuration for Cinchy

Initial setup

1. Retrieve and save the Federation Metadata XML file from the following location: https://{your.ADFS.server}/FederationMetadata/2007-06/FederationMetadata.xml.

2. If needed, use IIS Manager to establish an HTTPS connection for the Cinchy website.

3. Also establish an HTTPS connection for the SSO site. Make sure the port number aligns with the one specified in the login URL.

Configuration for appsettings.json

App Settings Section

External identity claim section

You will need to refer to the Rule Language URLs you copied from the ADFS Configuration. Replace the placeholders below with your own URLs:

{
  "AppSettings": {
    // Replace placeholders below with URLS
  },
  "ExternalIdentityClaimSection": {
    "FirstName": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"
    },
    "LastName": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname"
    },
    "Email": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress"
    },
    "MemberOf": {
      "ExternalClaimName": "http://schemas.microsoft.com/ws/2008/06/identity/claims/role"
    }
  }
}

Edit web.config

Insert the following lines within the <appSettings> section of your web.config file. Make sure to replace the {your.cinchy.url} and {your.cinchysso.url} with your Cinchy and Cinchy SSO values.

<appSettings>
  <!-- Replace placeholders below with URLS -->
  <add key="UseHttps" value="true" />
  <add key="StsAuthorityUri" value="https://{your.cinchy.url}" />
  <add key="StsRedirectUri" value="https://{your.cinchysso.url}/Account/LoginRedirect" />
  <!--  -->
</appSettings>

Group management

This section defines how to manage Groups.

Cinchy Groups

Cinchy Groups are containers that have Users and other Groups within them as members. Use Groups to provision access controls throughout the platform. Cinchy Groups enable centralized administration for access controls.

Groups are defined in the Groups table within the Cinchy domain. By default, only members of the Cinchy Administrators group can manage this table Each group has the following attributes:

Define a new AD Group

1. To define a new AD Group, create a new record within the Groups Table with the same name as the AD Group (using the cn attribute).

2. Set the Group Type to AD Group.

Convert an existing Group to sync with AD

1. To convert an existing group, update the Name attribute of the existing group record to match the AD Group (using the cn attribute).

2. Set the Group Type to AD Group.

Group membership sync

AD Groups defined in Cinchy have their members synced from AD through a batch process that leverages the Cinchy Command Line Interface (CLI).

Execution flow

The sync operation performs the following high-level steps:

1. Fetches all Cinchy registered AD Groups using a Saved Query.

2. Retrieves the usernames of all members for each AD Group. The default attribute for username that's retrieved is userPrincipalName, but configurable as part of the sync process.

3. For each AD Group, it loads the users that are both a member in AD and exist in the Cinchy Users table (matched on the Username) into the "Users" attribute of the Cinchy Groups table.

Dependencies

1. You must install the Cinchy CLI Model in your instance of Cinchy. See the CLI installation page for more details.

2. An instance of the Cinchy CLI must be available to execute the sync.

3. You must have a task scheduler to perform the sync on a regular basis (For example, AutoSys).

Configuration steps

Create a saved Query to retrieve AD Groups from Cinchy

1. Create a new query within Cinchy with the below CQL to fetch all AD Groups from the Groups table. The domain and name assigned to the query will be referenced in the next step.

SELECT [Name]
FROM [Cinchy].[Cinchy].[Groups]
WHERE [Group Type] = 'AD Group'
AND [Deleted] IS NULL

Create the sync config

1. Copy the below XML into a text editor of your choice and update the attributes listed in the table below the XML to align to your environment specific settings.

2. Create an entry with the config in your Data Sync Configurations table (part of the Cinchy CLI model).

<?xml version="1.0" encoding="utf-16" ?>
<BatchDataSyncConfig name="AD_GROUP_SYNC" version="1.0.0" xmlns="http://www.cinchy.co">
  <Parameters />
  <LDAPDataSource objectCategory="group" ldapserver="LDAP:\\activedirectoryserver.domain.com" username="encryptedUsername" password="encryptedPassword" >
    <Schema>
      <Column name="cn" ordinal="1" dataType="Text" maxLength="5000" isMandatory="false" validateData="false" trimWhitespace="true" description=""/>
      <Column name="member.userPrincipalName" ordinal="2" dataType="Text" maxLength="200" isMandatory="false" validateData="false" trimWhitespace="true" description=""/>
    </Schema>
    <Filter>
      lookup('Domain Name','Query Name')
    </Filter>
  </LDAPDataSource>
  <CinchyTableTarget model="" domain="Cinchy" table="Groups" suppressDuplicateErrors="false">
    <ColumnMappings>
      <ColumnMapping sourceColumn="cn" targetColumn="name" />
      <ColumnMapping sourceColumn="member.userPrincipalName" targetColumn="Users" linkColumn="Username" />
    </ColumnMappings>
    <SyncKey>
      <SyncKeyColumnReference name="name" />
    </SyncKey>
	<ChangedRecordBehaviour type="UPDATE" />
    <DroppedRecordBehaviour type="IGNORE" />
  </CinchyTableTarget>
</BatchDataSyncConfig>

Sync execution & scheduling

1. The below CLI command (see here for additional information on the syncdata command) should be used to execute the sync.

2. Update the command parameters (described in the table below) with your environment specific settings.

3. Execution of this command can be scheduled at your desired frequency using your scheduler of choice.

dotnet Cinchy.CLI.dll syncdata -s cinchyAppServer -u username -p "encryptedPassword" -m "Model" -f "AD_GROUP_SYNC" -d "TempDirectory"

Parameters

High number of Groups in ADFS

If you are syncing someone with a lot of ADFS groups, the server may reject the request for the header being too large. If you are able to login as a user with a few groups in ADFS but run into an error with users with a lot of ADFS groups (regardless of if those ADFS groups are in Cinchy), you will need to make the following changes:

Update the server max Request Header size

1. Follow the instructions outlined in this document.

CinchySSO AppSettings

In your CinchySSO app settings, you will also need to increase the max size of the request, as follows:

"AppSettings": {
  ...
  "MaxRequestHeadersTotalSize": {max size in bytes},
  "MaxRequestRequestBufferSize": {max size in bytes, use same as above},
  "MaxRequestBodySize": -1
}

Overview

Cinchy supports integration with any Identity Provider that issues SAML tokens (such as Active Directory Federation Services) for authenticating users.

It follows an SP Initiated SSO pattern where the SP will Redirect to the IdP and the IdP must submit the SAML Response via an HTTP Post to the SP Assertion Consumer Service.

Below is a diagram outlining the flow when a non-authenticated user attempt to access a Cinchy resource (Image 1).