'Easy' Releases¶

OCDS encourages the use of the releases and records model in order to publish up-to-date, timely data. However, sometimes publishers can't fully support the model, because historic data of contracting processes is not stored in the source system(s). In such case, the publisher can produce only one release for each contracting process, and the release gets overridden with new updates.

In this situation, it is possible to still meet OCDS requirements by following a strategy to build different release identifiers each time the data changes in a contracting process. Over the course of multiple updates, third parties would be able to build their own data store by periodically downloading or scraping the published data, and identifying the updates using release identifiers.

Here, two general approaches that a publisher can follow to renew release identifiers on each data update will be shown:

When the system saves a last modified date for entities - use the dates to create a new release ID. This can be as simple as appending the date to the release identifier.
When the system does not contain a last modified date - use a hash of all the fields to create a unique release ID. A hash is guaranteed to change when the data changes, and it is almost impossible for it to collide with a previous, existent identifier for the same contracting process.

See the examples below for more details.

These approaches can be useful for most situations but they are not meant to be the best solution for all cases. A publisher can find an alternative that works best for its own scenario.

Additional Considerations¶

Packaging¶

Releases in OCDS need to be packaged using a release package. This is to provide consistency and important metadata.

In an 'Easy' releases scenario it is still necessary to package data. Therefore the release needs to be wrapped in a release package. It is not appropriate to use an OCDS record to contain the release because record.releases is a list of all the releases and not just the latest one.

Release tags¶

Releases in OCDS have a release tag which indicates the stage of the contracting process they relate to.

When producing a single release for each contracting process, we can't use any tags that need other tags to be present in previous releases, e.g. 'tenderUpdate' which follows the 'tender' tag.

In this case, tags need to indicate the sections of the schema which are populated. For example, if a release provides data in the tender, award, and contract sections, the tag array would look like this:

{
    "tag": [
      "tender",
      "award",
      "contract"
    ]
}

This makes more sense if we realize that a release is always the "first release" of a contracting process for an user that access the data for the first time. All sections contain new data, and the release needs a tag for each one.

Worked examples¶

For the examples in the present section, the architecture in the image below is assumed.

Example architecture

Data is extracted from the source and transformed to OCDS each time there is a request, and resulting JSON files are not stored by the owner.

Scenario 1: when a last modified date is stored¶

The sample database structure used for the present example is illustrated in the image below.

Sample database 1

The 'ProcurementProcess' table contains one single row for each contracting process in the system, and the row is updated with each change. Contracts and suppliers are saved in separate tables. For both 'ProcurementProcess' and 'Contract' tables there is a lastModifiedDate column, with a timestamp of the last change made for the row.

The following steps show the progress in a single contracting process, and how the unique release identifier is built for each update.

1. Tender initiation¶

The contracting process begins with a tender notice. The source tables contain the following data:

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency	createdAt	lastModifiedAt
371630	Cleaning services	tender	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena				2019-12-01T09:00:00Z	2019-12-01T09:00:00Z

There is no supplier or contract yet, so there are no entries for this contracting process in them. In this stage, the ocid is build by appending the value of the field processID to the ocid prefix ('ocds-213czf'), since processID is identifying uniquely each contracting process.

{
    "ocid": "ocds-213czf-371630"
}

For the release ID, the value for lastModifiedAt is appended to the end of the ocid:

{
    "id": "ocds-213czf-371630/2019-12-01T09:00:00Z"
}

It is possible to use the date alone as the release identifier, but prepending the ocid makes easier to differentiate releases from various processes in the same release package.

See the full JSON file below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-01T09:00:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-01T09:00:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/2019-12-01T09:00:00Z",
            "tag": [
                "tender"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            }
        }
    ]
}

2. Tender update¶

The tender has been updated: the value increased slightly and the description has changed.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency	createdAt	lastModifiedAt
371630	Cleaning services	tender	Cleaning services for the City Hall, from 01/2020 to 12/2020	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	145000000	PYG	1000	City Hall of Juan de Mena				2019-12-01T09:00:00Z	2019-12-03T09:00:00Z

The lastModifiedDate value has changed as well, therefore the value of the release identifier will change:

{
    "id": "ocds-213czf-371630/2019-12-03T09:00:00Z"
}

See the full JSON below:

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-04T09:00:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-03T09:00:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/2019-12-03T09:00:00Z",
            "tag": [
                "tender"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            }
        }
    ]
}

Note that the 'tag' field is still 'tender'.

3. Award¶

Now, the tender has been awarded. The related columns in 'ProcurementProcess' table have been populated and there is a new row in the 'Supplier' table for the process.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency	createdAt	lastModifiedAt
371630	Cleaning services	award	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena	2019-12-26T14:00:00Z	PYG	144300000	2019-12-01T09:00:00Z	2019-12-27T14:42:00Z

Supplier

supplierID	processID	name	companyID	contactName	contactEmail
144	371630	Bosques del Parana SRL	80020095-0	Juan Torres	jtorres@example.com

As the 'ProcurementProcess' table has been updated, the related release will have a new id:

{
    "id": "ocds-213czf-371630/2019-12-14T14:42:00Z"
}

And the 'awards' section will be filled with the corresponding data. See the full JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-27T14:42:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-27T14:42:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/2019-12-27T14:42:00Z",
            "tag": [
                "tender",
                "award"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                },
                {
                    "id": "PY-RUC-80020095-0",
                    "name": "Bosques del Parana SRL",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80020095-0"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            },
            "awards": [
                {
                    "id": "371630",
                    "date": "2019-12-26T14:00:00Z",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "value": {
                        "amount": 144300000,
                        "currency": "PYG"
                    },
                    "suppliers": [
                        {
                            "id": "PY-RUC-80020095-0",
                            "name": "Bosques del Parana SRL"
                        }
                    ]
                }
            ]
        }
    ]
}

Note that we are keeping the 'tender' tag from the previous step.

4. Contract¶

At the last stage there is a signed contract. The 'ProcurementProcess' table changes again to reflect the new stage, and a new entry is added in the 'Contract' table as shown below.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency	createdAt	lastModifiedAt
371630	Cleaning services	contract	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena	2019-12-26T14:00:00Z	PYG	144300000	2019-12-01T09:00:00Z	2020-01-11T07:53:50Z

Contract

contractID	processID	status	signedDate	value	currency	startDate	endDate	createdAt	lastModifiedAt
100	371630	active	2019-01-10T10:00:00Z	116400000	PYG	2019-01-15T08:00:00Z	2020-01-14T17:00:00Z	2020-01-11T07:53:50Z	2020-01-11T07:53:50Z

A new release id is generated:

{
    "id": "ocds-213czf-371630/2020-01-11T07:53:50Z"
}

See the full JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2020-01-11T07:53:50Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2020-01-11T07:53:50Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/2020-01-11T07:53:50Z",
            "tag": [
                "tender",
                "award",
                "contract"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                },
                {
                    "id": "PY-RUC-80020095-0",
                    "name": "Bosques del Parana SRL",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80020095-0"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            },
            "awards": [
                {
                    "id": "371630/100",
                    "date": "2019-12-26T14:00:00Z",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "value": {
                        "amount": 144300000,
                        "currency": "PYG"
                    },
                    "suppliers": [
                        {
                            "id": "PY-RUC-80020095-0",
                            "name": "Bosques del Parana SRL"
                        }
                    ]
                }
            ],
            "contracts": [
                {
                    "id": "371630/100",
                    "awardID": "371630/100",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "status": "active",
                    "value": {
                        "amount": 116400000,
                        "currency": "PYG"
                    },
                    "dateSigned": "2019-01-10T10:00:00Z",
                    "period": {
                        "startDate": "2019-01-15T08:00:00Z",
                        "endDate": "2020-01-14T17:00:00Z"
                    }
                }
            ]
        }
    ]
}

Scenario 2: when a last modified date is NOT stored¶

This approach can be used when there is no last modified date in the source data. Below there is an updated image from the previous example:

Sample database 2

The example is almost the same as the previous one, with the same steps, but with no last modified date in the tables as seen in the image above.

1. Tender initiation¶

The example starts with the tender, and the following data in the 'ProcurementProcess' table:

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency	createdAt	lastModifiedAt
371630	Cleaning services	tender	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena				2019-12-01T09:00:00Z	2019-12-01T09:00:00Z

The unique identifier for this stage can be generated by joining all fields into a single string, and applying a hash function on it. Depending of tools and/or programming languages used in the transformation process, there can be many ways to achieve this task. An example of how it can be done using a PostgreSQL query is shown below:

SELECT md5(CAST((p.*)AS text))
FROM ProcurementProcess p
WHERE p.processID = 371630
;

It is important to include all data fields that are included in OCDS data in the hash calculation. For the current row, the output value is 69a19ab9713d08bc7c54793144997d3a. As the date field in the previous example, this will be appended at the end of the ocid:

{
    "id": "ocds-213czf-371630/69a19ab9713d08bc7c54793144997d3a"
}

See the full JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-01T09:00:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-01T09:00:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/69a19ab9713d08bc7c54793144997d3a",
            "tag": [
                "tender"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            }
        }
    ]
}

2. Tender update¶

Now that tender data has changed: there are updates in the value and description fields.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency
371630	Cleaning services	tender	Cleaning services for the City Hall, from 01/2020 to 12/2020	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	145000000	PYG	1000	City Hall of Juan de Mena

The same hash operation is repeated over the updated row and the resulting value is 957969e7458f5144a931d2feb452ea48. The new release identifier is:

{
    "id": "ocds-213czf-371630/957969e7458f5144a931d2feb452ea48"
}

See the full JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-04T09:00:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-04T09:00:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/957969e7458f5144a931d2feb452ea48",
            "tag": [
                "tender"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            }
        }
    ]
}

3. Award¶

The tender has been awarded, therefore the 'ProcurementProcess' table has been updated and a new entry in the 'Supplier' table is included.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency
371630	Cleaning services	award	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena	2019-12-26T14:00:00Z	PYG	144300000

Supplier

supplierID	processID	name	companyID	contactName	contactEmail
144	371630	Bosques del Parana SRL	80020095-0	Juan Torres	jtorres@example.com

The new data in the 'Supplier' table can be included in the hash generation as well. In PostgreSQL, the previous sentence can be changed to include the 'Supplier' table as follows:

WITH data AS (
  SELECT * from ProcurementProcess
  JOIN Supplier on Supplier.processID = ProcurementProcess.processID
  WHERE processID = 371630
)

SELECT md5(CAST((data.*)AS text)) FROM data
;

The result of the query is 610d5900f947bcf67100449999ea49ce, and the new release identifier is:

{
    "id": "ocds-213czf-371630/610d5900f947bcf67100449999ea49ce"
}

See the full JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2019-12-27T14:42:00Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2019-12-27T14:42:00Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/610d5900f947bcf67100449999ea49ce",
            "tag": [
                "tender",
                "award"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                },
                {
                    "id": "PY-RUC-80020095-0",
                    "name": "Bosques del Parana SRL",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80020095-0"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            },
            "awards": [
                {
                    "id": "371630",
                    "date": "2019-12-26T14:00:00Z",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "value": {
                        "amount": 144300000,
                        "currency": "PYG"
                    },
                    "suppliers": [
                        {
                            "id": "PY-RUC-80020095-0",
                            "name": "Bosques del Parana SRL"
                        }
                    ]
                }
            ]
        }
    ]
}

4. Contract¶

In the last stage the contract is signed, the 'ProcurementProcess' table is updated and a new entry in the 'Contract' table is added.

ProcurementProcess

processID	title	stage	description	procurementMethod	bidSubmissionOpenDate	bidSubmissionCloseDate	tenderValue	tenderCurrency	buyerID	buyerName	awardDate	awardValue	awardCurrency
371630	Cleaning services	contract	Cleaning services for the City Hall	direct	2019-12-14T08:00:00Z	2019-12-14T08:30:00Z	144300000	PYG	80023736-6	City Hall of Juan de Mena	2019-12-26T14:00:00Z	PYG	144300000

Contract

contractID	processID	status	signedDate	value	currency	startDate	endDate
100	371630	active	2019-01-10T10:00:00Z	116400000	PYG	2019-01-15T08:00:00Z	2020-01-14T17:00:00Z

Since there is one more table involved ('Contract'), the three tables that store data for the full process can be used to calculate the hash. The previous SQL query can be changed again to include the 'Contract' table:

WITH data AS (
  SELECT * from ProcurementProcess
  JOIN Supplier on Supplier.processID = ProcurementProcess.processID
  JOIN Contract on Contract.processID = ProcurementProcess.processID
  WHERE processID = 371630
)

SELECT md5(CAST((data.*)AS text)) FROM data
;

Although it is true that the data in the 'Supplier' table has not changed in the last step, it ought to be included unless it is guaranteed that the data from the table does not change after a certain step in the process (with suppliers, this could be possible in some scenarios but that assumption is not taken here).

The new hash value is 1a87b0662990c66e140e62e813165107, and the new release identifier is:

{
    "id": "ocds-213czf-371630/1a87b0662990c66e140e62e813165107"
}

See the final JSON below.

{
    "uri": "https://standard.open-contracting.org/examples/",
    "version": "1.1",
    "publisher": {
        "uid": "80048717-6",
        "name": "Direccion Nacional de Contrataciones P\u00fablicas",
        "scheme": "PY-RUC",
        "uri": "https://standard.open-contracting.org/examples/"
    },
    "publishedDate": "2020-01-11T07:53:50Z",
    "license": "http://opendatacommons.org/licenses/pddl/1.0/",
    "publicationPolicy": "https://github.com/open-contracting/sample-data/",
    "releases": [
        {
            "date": "2020-01-11T07:53:50Z",
            "ocid": "ocds-213czf-371630",
            "id": "ocds-213czf-371630/1a87b0662990c66e140e62e813165107",
            "tag": [
                "tender",
                "award",
                "contract"
            ],
            "initiationType": "tender",
            "parties": [
                {
                    "id": "PY-RUC-80023736-6",
                    "name": "City Hall of Juan de Mena",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80023736-6"
                    }
                },
                {
                    "id": "PY-RUC-80020095-0",
                    "name": "Bosques del Parana SRL",
                    "identifier": {
                        "scheme": "PY-RUC",
                        "id": "80020095-0"
                    }
                }
            ],
            "buyer": {
                "id": "PY-RUC-80023736-6",
                "name": "City Hall of Juan de Mena"
            },
            "tender": {
                "id": "101",
                "title": "Cleaning services",
                "description": "Cleaning services for the City Hall, from 01/2020 to 12/2020",
                "status": "active",
                "procurementMethod": "direct",
                "value": {
                    "amount": 144300000,
                    "currency": "PYG"
                },
                "tenderPeriod": {
                    "startDate": "2019-12-14T08:00:00Z",
                    "endDate": "2019-12-14T08:30:00Z"
                }
            },
            "awards": [
                {
                    "id": "371630/100",
                    "date": "2019-12-26T14:00:00Z",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "value": {
                        "amount": 144300000,
                        "currency": "PYG"
                    },
                    "suppliers": [
                        {
                            "id": "PY-RUC-80020095-0",
                            "name": "Bosques del Parana SRL"
                        }
                    ]
                }
            ],
            "contracts": [
                {
                    "id": "371630/100",
                    "awardID": "371630/100",
                    "title": "Cleaning services",
                    "description": "Cleaning services for the City Hall",
                    "status": "active",
                    "value": {
                        "amount": 116400000,
                        "currency": "PYG"
                    },
                    "dateSigned": "2019-01-10T10:00:00Z",
                    "period": {
                        "startDate": "2019-01-15T08:00:00Z",
                        "endDate": "2020-01-14T17:00:00Z"
                    }
                }
            ]
        }
    ]
}